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



## cdg786 (Dec 13, 2022)

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 (Dec 13, 2022)

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


----------



## cdg786 (Dec 13, 2022)

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


----------



## Dan_W (Dec 13, 2022)

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 (Dec 13, 2022)

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 (Dec 13, 2022)

cdg786 said:


> .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 (Dec 13, 2022)

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 (Dec 13, 2022)

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 (Dec 13, 2022)

cdg786 said:


> 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 (Dec 14, 2022)

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.


----------



## cdg786 (Dec 13, 2022)

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 (Dec 14, 2022)

Hy,
try just adding after this line

```
With OutMail
.Display
```


----------



## Dan_W (Dec 14, 2022)

cdg786 said:


> 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 (Dec 14, 2022)

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


----------



## Dan_W (Dec 14, 2022)

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


----------



## ChaseCasey (Dec 15, 2022)

cdg786 said:


> 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
```


----------

