ADD EMAIL SIGNATURE TO CODE GENERATED EMAIL

[JuicyMusic]

Hi, How can I adjust this code to insert a saved email signature to the generated emails please? The saved signature will be MyComplianceSig
The body of the emails comes from typing something in a big text box. This code works perfectly but I can't find how to have my saved email signature be included in every generated email.

I will keep looking around to see if i can insert the section myself in the mean-time. I'm sure this must be a very generic section to add. Any response is appreciated, even though

I will upload a snapshot of my email distribution list sheet. Sorry, my company won't allow me to install XL2BB. Thank you so much!!

Option Explicit

Sub EMAIL_Send_to_DistributionList()


    Dim OutlookApp As Object
    Dim MItem As Object
    Dim cell As Range
    Dim email_ As String
    Dim cc_ As String, bcc_ As String
    'Coco deactivated the 3rd and 4th "As String" of the line of code above.  Not needed because 2 TextBoxes were used instead.  See last section of this code.
    'subject_ As String, body_ As String
 
  'CG added this section of code to create a error message box
If WorksheetFunction.CountA(Range("A2:A350")) = 0 Then
    MsgBox "Please enter a minimum of one email address in Column A.", vbCritical, "Missing Email Address"
        Exit Sub
    End If
 
 'CG added this section of code to create a Yes/No Message Box
 Dim answer As Variant
    answer = MsgBox("Are you ready to generate your email?", vbYesNo + vbQuestion, "Email Disribution")
    If answer = vbNo Then
        Exit Sub
    End If
    

     'Create Outlook object
    Set OutlookApp = CreateObject("Outlook.Application")

     'Loop through the rows
    For Each cell In Range("A2:A350").Cells.SpecialCells(xlCellTypeConstants)

        email_ = cell.Value
        cc_ = cell.Offset(0, 1).Value
        bcc_ = cell.Offset(0, 2)
        'Coco deactivated the next two lines of code and used TexBoxes instead
       ' subject_ = cell.Offset(0, 3)
       ' body_ = cell.Offset(0, 4)

        'Create Mail Item and send it
        Set MItem = OutlookApp.CreateItem(0)
        With MItem
            .To = email_
            .CC = cc_
            .BCC = bcc_
            'CG changed the following line of code to recognize the 2nd TextBox
            .Subject = ActiveSheet.TextBoxes(2).Text
            'CG changed the following line of code to recognize the 1st TextBox
            .Body = ActiveSheet.TextBoxes(1).Text
            'CG changed the following line of code from .Send to .Display temporarily
            .Display
        End With
    Next
End Sub

 

[JamesCanale]

change:
.Body = ActiveSheet.TextBoxes(1).Text
to
.Body = ActiveSheet.TextBoxes(1).Text & vbNewLine & MyComplianceSig

if MyComplianceSig is a string with your signature in there.

 

[JuicyMusic]

Hi, thank you for your reply. My email signature will be nowhere on the sheet that I snapped for you.

Is it possible to have the code add the email signature after (or while) the email is being generated?

 

[JamesCanale]

I can't say I know a way to access your Outlook signature from excel VBA. Could you reproduce it in a string in your excel vba code?

 

[Fluff]

Have a look here Insert Outlook Signature in mail

 

[JuicyMusic]

I'll try that right now adding my signature to a string like you ask.. BRB.
I should let you know that different people will be using this template to send emails. They will have different email signatures

 

[JuicyMusic]

Hello, I was out for two days but I'm back now. It did not work.

I've been reading and noticing that many blogs or sites say that having Outlook add the email signature is not possible....Can you let me know if this is possible or not? THanks!!

 

[Fluff]

If Ron de Bruin says it's possible, then Yes it is possible.