none
VBA To Add Attachment, Text, and Signature RRS feed

  • Question

  • Hello, On a regular basis I send a confirmation reply email to a group of users when a new user is setup in our system. I am trying to find a VBA script the I can run when I have this type reply ready to go. The VBA would 1) Add an a Word file as an attachment to the reply 2) Add a a block of text 3) Add my Outlook signature. I found the script below that I hope would get me started, but now I'm getting the below error message. Can anyone help me find an appropriate script as well help dealing with this error? Thank you.
    Friday, June 16, 2017 5:53 PM

All replies

  • Hello,

    It seems you need to adjust settings in the Trust center. Try to choose Enable all macro (not  recommended; potentially dangerous code can run) in Macro Settings. Don't forget to restart Outlook after making your changes.

    1) Add an a Word file as an attachment to the reply

    The Add method of the Attachments class creates a new attachment in the  Attachments collection. When an  Attachment is added to the Attachments collection of an item, the Type property of the Attachment will always return olOLE (6) until the item is saved. To ensure consistent results, always save an item before adding or removing objects in the Attachments collection.

    2) Add a a block of text 

    The Outlook object model provides three main ways for dealing with message bodies:

    1. Body.
    2. HTMLBody
    3. Word editor. The Inspector class provides the WordEditor property which returns an instance of the Document class from the Word object model which represents the message body. Outlook uses Word as an email editor. 

    You can read more about that in the Chapter 17: Working with Item Bodies .

    3) Add my Outlook signature

    If you create a signature in Outlook it will save three files (HTM, TXT and RTF) into

    Vista and Windows 7/8:
    C:\Users\<UserName>\AppData\Roaming\Microsoft\Signatures

    You just need to read an appropriate file from the disk and paste it to the end of the message. 

    Sub MailWithHTMLSignature()
        Dim OutApp As Object
        Dim OutMail As Object
        Dim strbody As String
        Dim SigString As String
        Dim Signature As String
    
        Set OutApp = Application
        Set OutMail = OutApp.CreateItem(0)
    
        strbody = "<H3><B>Dear Customer</B></H3>" & _
                  "Please visit this website to download the new version.<br>" & _
                  "Let me know if you have problems.<br>" & _
                  "<A HREF=""URL"">Page</A>" & _
                  "<br><br><B>Thank you</B>"
    
        'Change only Mysig.htm to the name of your signature
        SigString = Environ("appdata") & _
                    "\Microsoft\Signatures\Mysig.htm"
    
        If Dir(SigString) <> "" Then
            Signature = GetBoiler(SigString)
        Else
            Signature = ""
        End If
    
        On Error Resume Next
    
        With OutMail
            .To = "eugene@test.com"
            .CC = ""
            .BCC = ""
            .Subject = "This is the Subject line"
            .HTMLBody = strbody & "<br>" & Signature
            .Send    'or use .Display
        End With
    
        On Error GoTo 0
        Set OutMail = Nothing
        Set OutApp = Nothing
    End Sub
    
    
    Function GetBoiler(ByVal sFile As String) As String
        Dim fso As Object
        Dim ts As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
        GetBoiler = ts.readall
        ts.Close
    End Function

    See Insert Outlook Signature in mail for more information. 


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Saturday, June 17, 2017 3:21 PM