Include Email Signature after VBA Created Email w/ PDF via Outlook

[cdg786]

Have been setting up an automated bid request and purchase order matrix. All is working well. However, I want to tighten up the style and professionalism of the auto response email in hopes of keeping the automated email out of people's spam filters. I think the problem would be solved if I could get my outlook to add in my standard email siganture onto the email before being sent. Does anyone know if this is an excell VBA issue or an Outlook issue? I have tried solving it from the Outlook side with little success.

If it is an excel VBA coding issue, does anyone know how to stylistically program in a formatted email signature (color, bold, etc...) into the macro? Code is as follows:

Public Sub Save_Range_As_PDF_and_Send_Email_PO()

    Dim PDFrange As Range
    Dim PDFfile As String
    Dim toEmail As String, emailSubject As String
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim HTMLBody As String
    
    With ActiveWorkbook
    
        Set PDFrange = .ActiveSheet.Range("A1:I53")
        toEmail = .ActiveSheet.Range("B15").Value
        emailSubject = .ActiveSheet.Range("C6").Value
        
        PDFfile = Replace(.FullName, ".xlsx", ".pdf")
    
    End With
    
    PDFrange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    'Send email with PDF file attached

    Set OutApp = New Outlook.Application

    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
        .To = toEmail
        .Subject = emailSubject
        .HTMLBody = "<p> Hello, </p>" & _
                    "<p> Please see attached purchase order. We are happy to connect to discuss an missing details. </p>" & _
                    "<p> Thank you, </p>" & _
                    "<p> rdc Procurement. </p>"
                    
        .Attachments.Add PDFfile
        .send
    End With

    'Delete the temporary PDF file

    Kill PDFfile

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

 

[Sequoyah]

Hy,
try just adding after this line

With OutMail
.Display

 

[Dan_W]

Thanks for your thoughtful reply. That is exactly why I am learning VBA now. Trying to help my team free up much needed time on projects. The method above did not generate the desired result. The email is still sending cleanly. But no signature. It is a curious issue. I like the word file solution. However, I need 3-5 other people to use the same excel matrix. I need their signatures to show up after the generate the pdf too.

Curious problem.

Did you copy and paste the code as above, or did you type it in? I ask because you may not have seen that there was a period before HTMLBody. So it should look like:

.HTMLBody = "<p> Hello, </p> <p> blah blah blah </p>" & .HTMLBody

If the above still doesn't work try deleting the whole line. In theory, that should just show signature and not the template text.

And, as helpfully suggested by @Sequoyah above, it's generally best to replace the .Send with .Display so all it is doing is displaying the email as it would be sent, rather than sending it each time. (Thanks, S!

 

[cdg786]

So you won't believe it. But I simply added .Display after With OutMail. And it works.

 

[Dan_W]

That's awesome. It's also a relief, because I was running out of font sizes...lol.

 

[ChaseCasey]

Thanks for your thoughtful reply. That is exactly why I am learning VBA now. Trying to help my team free up much needed time on projects. The method above did not generate the desired result. The email is still sending cleanly. But no signature. It is a curious issue. I like the word file solution. However, I need 3-5 other people to use the same excel matrix. I need their signatures to show up after the generate the pdf too.

Curious problem.

Try stealing from this - I made this for my team awhile back and should resolve your current issue regarding inserting the users signature. Replace any Range values that exist in it currently and put your own data into it.

Sub Email()

    Dim OApp As Object, OMail As Object, signature As String
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileFullPath As String

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

'Temporary file path where pdf file will be saved before sending it in email by attaching it.

    TempFilePath = Environ$("temp") & "\"

'Naming convention can be changed based on your requirement.

    TempFileName = "Insert File Name Here" & Range("R4").Text & ".pdf"

'Complete path of the file where it is saved
    
    FileFullPath = TempFilePath & TempFileName

'Now Export the Activesheet as PDF with the given File Name and path

    On Error GoTo err
    With ActiveSheet
        .ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=FileFullPath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    End With

'Now open a new mail

    Set OApp = CreateObject("Outlook.Application")
    Set OMail = OApp.CreateItem(0)
    With OMail
        .Display
    End With
    
    signature = OMail.HTMLBody
    strbody = "<BODY style=font-size:11pt;font-family:Arial>Good " & Range("R3").Value _
    & ",</p><p>Insert text here" & Range("R4").Value _
    & ".</p><p><b>Insert text here </b>" & Range("R22").Value _
    & "</p><p>Insert text here.</p><p>Thank you,"

    With OMail
        .To = Range("R35").Value
        .CC = Range("R30").Value & ";" & Range("R28").Value & ";" & Range("R7").Value & ";" & Range("R14").Value & ";" & Range("R18").Value & ";" & Range("R33").Value
        .BCC = ""
        .Subject = "Insert Subject Here" & Range("R4").Text
        .HTMLBody = strbody & signature
        .Attachments.Add FileFullPath '--- full path of the pdf where it is saved
        .Display   'or use .Send to email without reviewing as a draft.
    End With
    
'Set nothing to the objects created

    Set OMail = Nothing
    Set OApp = Nothing
    
    On Error GoTo 0

'Since mail has been sent with the attachment, now delete the pdf file from the temp folder

    Kill FileFullPath

'Now set the application properties back to true

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
    Range("O4").Interior.ColorIndex = 10
    
    MsgBox "Email has been generated successfully!"
    Exit Sub
err:
        MsgBox err.Description

End Sub