Clash Royale CLAN TAG#URR8PPP

Remove the signature when opening an Outlook template in Excel

I'm trying to open an Outlook template (.oft) file from Excel but without appending the user's signature. I can't get this to work.

I know I need to delete the hidden bookmark "_MailAutoSig" but I can't figure out how. I've tried to follow this guide but it's out of date and doesn't work with Outlook / Excel 2016: https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/dd492012(v=office.12)#176-working-with-outlook-signatures

Here is my code

Option Explicit

Sub openEmail()

Dim cfgFromEmail As String
Dim cfgNotice As String
Dim cfgTemplate As String
Dim appOutlook As Outlook.Application
Dim newEmail As Outlook.MailItem

Dim rownum As Integer
Dim colnum As Integer

rownum = 6

cfgFromEmail = Sheets("Email").Range("O5").Value
cfgNotice = Sheets("Email").Cells(rownum, 10) '10 = column J
cfgTemplate = Sheets("Email").Cells(rownum, 11) '11 = column K

Set appOutlook = CreateObject("Outlook.Application")
Set newEmail = appOutlook.CreateItemFromTemplate("\locationtotemplate" & cfgTemplate & ".oft")
'Set template = mailApp.CreateItem(olMailItem) 'Creates a blank email

If cfgNotice <> "null" Then 'If is not blank
MsgBox cfgNotice, vbInformation, "Before you send the email"
End If

With newEmail
.SentOnBehalfOfName = cfgFromEmail
.Display 'Show the email

End With

Set newEmail = Nothing
Set appOutlook = Nothing

End Sub


Any help is greatly appreciated. I have spent several hours searching Google and Stack Overflow to no luck.

2 Answers
2

If the email template is not too complicated, you may be able to just create a new email and create the template without signature using HTML:

Sub emailgenerator

Dim appOutlook As Outlook.Application
Dim newEmail As Outlook.MailItem
Dim emailBody As String

Set appOutlook = CreateObject("Outlook.Application")
Set newEmail = olApp.CreateItem(olMailItem)

emailBody = "<p>Header</p><br><p>body area or something</p>"
emailBody = emailBody & "<table></table>" ' maybe add tables and whatever is needed

With newEmail
.To = "abc@abc.com"
.CC = "def@def.com"
.Subject = "Test"
.SentOnBehalfOfName = "youremail@youremail.com" ' could disregard this
.HTMLBody = emailBody
.Save
.Close olPromptForSave
End With

End Sub


This will take some looking into HTML but you can probably recreate the template with enough effort.

I believe when I tried this method for another project my signature wasn't getting appended automatically as it would with a template but not sure... best of luck

I have found a solution thanks to this stack overflow post

We need to save our template as HTML, then manually create a new email using the HTML code.

I'm yet to add images to the code but I think this will be easy using a find and replace method.

Final code without images:

Option Explicit

Sub openEmail(rownum As Integer)

Dim cfgFromEmail As String
Dim cfgNotice As String
Dim cfgTemplate As String
Dim appOutlook As Outlook.Application
Dim newEmail As Outlook.MailItem
Dim htmlPath As String

'Dim rownum As Integer
'Dim colnum As Integer

'rownum = 6

cfgFromEmail = Sheets("Email").Range("O5").Value
cfgNotice = Sheets("Email").Cells(rownum, 10) '10 = column J
cfgTemplate = Sheets("Email").Cells(rownum, 11) '11 = column K
htmlPath = "\shareddrivepathtotemplategoeshere" & cfgTemplate & ".htm"

Set appOutlook = CreateObject("Outlook.Application")
Set newEmail = appOutlook.CreateItem(olMailItem) 'Creates a blank email

If cfgNotice <> "null" Then 'If is not blank
MsgBox cfgNotice, vbInformation, "Before you send the email"
End If

With newEmail
.SentOnBehalfOfName = cfgFromEmail
.HTMLBody = HTMLtoString(htmlPath)
'Refer to and fill in variable items in template
'.Body = Replace(.Body, "<< clientname >>", Worksheets("Clients").Range(1, 2))
'.HTMLBody = Replace(.HTMLBody, "&lt;&lt; clientname &gt;&gt;", Worksheets("Clients").Range(1, 2))
.Display 'Show the email

End With

Set newEmail = Nothing
Set appOutlook = Nothing

End Sub

Function HTMLtoString(htmlPath As String)
'Returns a string after reading the contents of a given file
HTMLtoString = CreateObject("Scripting.FileSystemObject").OpenTextFile(htmlPath).ReadAll()

End Function


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.