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

 

[Dan_W]

When you create a new email normally, is your email signature not added to it automatically?

 

[cdg786]

It is automatically attached for all new messages. Makes me wonder if it is a deeper outlooks setting...

 

[Dan_W]

Ahh. In which case, I'm reasonably certain that in the signature dialog box where tou adjust the settings, it's just a matter of changing the particular setting that says (1) new emails or (2) all emails.

That's said, I have a sneaking suspicion that even if you did change that setting then your code about might nonetheless effectively write over it. It would be an easy fix, I'd imagine, but perhaps worth just checking that you can change the setting first

 

[cdg786]

Checked the signature settings. All set correctly for new/responses. Seems the code is over riding. Any ideas on how to fix this?

 

[Dan_W]

.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>"

It'll be this line of code, I imagine. This is VBA telling outlook that the entire body of the email will be this text/code. I gather you'd want your email signature to go at the end of this?

 

[cdg786]

Exactly. So VBA is over riding but saying "this and only this." Do you have a suggestion on how to write the .HTMLBody line in a less exclusionary manner? Thank you for your help.

 

[Dan_W]

I do, I was just trying to stall you while I got back to my computer because I was on my way home and really didn't want to have to type it all on my mobile phone :-/

.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>" & "<p></p>" & .HTMLBody

I would just suggest changing the existing line by add & .HTMLBody onto the end of it. I have put in an additional

& "<p></p>"

which will introduce another paragraph break between the message and your signature, but you may decide it looks odd, so you could delete that part if you prefer.

 

[Dan_W]

I want to tighten up the style and professionalism of the auto response email

I don't know if the above accomplishes all that you were hoping to do, but wanting to improve the style/professionalism of my work product is how I got into VBA to begin with (that, and i wanted to automate my job because I'm just so lazy! ), but just wanted to point out that - if you weren't already aware - you could design a template for the bid request and purchase order matrix in Word, and generate the PDF file there if you thought it would help... If you know what the automated response would need to look like, then it would be a fairly trivial task to swap out the Word templates placeholders with whatever data you wanted, and generate the PDF from that. Just a thought.

Anyway, do let me know if the above solves your issue.

 

[cdg786]

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.