insert outlook signature for email list

[godzilla185]

Hi everyone,

I have played around with Ron de Bruin's and other insert signature code I saw on this website but cannot make my code work.

I made a VBA macro that creates emails from a table list in excel as below, It adjusts subject line, date and everything works as I want, except I'm having trouble adjusting the code to include the signature... If anyone can help.


Sub emailgen1()
' creates emails with updated dates as per the name list. Creates 1 email per name in column H

Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long, Signature As String
lr = Cells(Rows.Count, "H").End(xlUp).Row
Set Mail_Object = CreateObject("Outlook.Application")

Dim wd As Date
wd = WorksheetFunction.WorkDay(Date, -1)

For i = 2 To lr

With Mail_Object.CreateItem(o)
.Subject = Range("I" & i).Value & Format(Date, "dd mmm yyyy") & " text " & Format(wd, "dd mmm yyyy")
.To = Range("H" & i).Value
.body = "Good morning," & vbNewLine & vbNewLine & " & Format(wd, "dd mmm yyyy")" & Range("J" & i).Value & vbNewLine & Signature
.CC = "client"
.Display
End With


Next i
MsgBox "E-mail successfully created", 64
Application.DisplayAlerts = False
Set Mail_Object = Nothing

End Sub

 

[breynolds0431]

Hi. I don't see where you are setting the Signature. You will also want to use Ron's GetBoiler function.

Try the below and ensure that you add Ron's function (see end) to the same module.

Sub emailgen1()
' creates emails with updated dates as per the name list. Creates 1 email per name in column H

Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long, Signature As String
lr = Cells(Rows.Count, "H").End(xlUp).Row
Set Mail_Object = CreateObject("Outlook.Application")

-->'Change only Mysig.htm to the name of your signature (for example, mine is titled default.htm)
        SigString = Environ("appdata") & _
                "\Microsoft\Signatures\Mysig.htm"

      If Dir(SigString) <> "" Then
          Signature = GetBoiler(SigString)
      Else
          Signature = ""
      End If

Dim wd As Date
wd = WorksheetFunction.WorkDay(Date, -1)

For i = 2 To lr

With Mail_Object.CreateItem(o)
.Subject = Range("I" & i).Value & Format(Date, "dd mmm yyyy") & " text " & Format(wd, "dd mmm yyyy")
.To = Range("H" & i).Value
.body = "Good morning," & vbNewLine & vbNewLine & " & Format(wd, "dd mmm yyyy")" & Range("J" & i).Value & vbNewLine & Signature
.CC = "client"
.Display
End With


Next i
MsgBox "E-mail successfully created", 64
Application.DisplayAlerts = False
Set Mail_Object = Nothing

End Sub

Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

 

[godzilla185]

Hi. I don't see where you are setting the Signature. You will also want to use Ron's GetBoiler function.

Try the below and ensure that you add Ron's function (see end) to the same module.

Sub emailgen1()
' creates emails with updated dates as per the name list. Creates 1 email per name in column H

Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long, Signature As String
lr = Cells(Rows.Count, "H").End(xlUp).Row
Set Mail_Object = CreateObject("Outlook.Application")

-->'Change only Mysig.htm to the name of your signature (for example, mine is titled default.htm)
        SigString = Environ("appdata") & _
                "\Microsoft\Signatures\Mysig.htm"

      If Dir(SigString) <> "" Then
          Signature = GetBoiler(SigString)
      Else
          Signature = ""
      End If

Dim wd As Date
wd = WorksheetFunction.WorkDay(Date, -1)

For i = 2 To lr

With Mail_Object.CreateItem(o)
.Subject = Range("I" & i).Value & Format(Date, "dd mmm yyyy") & " text " & Format(wd, "dd mmm yyyy")
.To = Range("H" & i).Value
.body = "Good morning," & vbNewLine & vbNewLine & " & Format(wd, "dd mmm yyyy")" & Range("J" & i).Value & vbNewLine & Signature
.CC = "client"
.Display
End With


Next i
MsgBox "E-mail successfully created", 64
Application.DisplayAlerts = False
Set Mail_Object = Nothing

End Sub

Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.readall
    ts.Close
End Function

Hi,

I have tried this, but a few issues pop up:
1st - I cannot get to the microsoft directy folder manually as it is my work computer.
2nd - when i try and match name as per the way i named the signature "Group.htm" the macro generates my emails and i get a bunch of code in place of the signature.

Are you able to assist? Appreciate your time and help

 

[breynolds0431]

Have you modified your folder settings to show hidden items? I believe the app data folder is hidden by default.

I checked one of my files that sends emails and I discovered that my SigString variable is different than Ron's. Can you try replacing the SigString declaration previously provided with the below?

'Change "default.htm" to the name of your signature
Dim SigString As String: SigString = "C:\Users\" & Environ("Username") & "\AppData\Roaming\Microsoft\Signatures\default.htm"

 

[godzilla185]

Have you modified your folder settings to show hidden items? I believe the app data folder is hidden by default.

I checked one of my files that sends emails and I discovered that my SigString variable is different than Ron's. Can you try replacing the SigString declaration previously provided with the below?

'Change "default.htm" to the name of your signature
Dim SigString As String: SigString = "C:\Users\" & Environ("Username") & "\AppData\Roaming\Microsoft\Signatures\default.htm"

Unfortunately I keep getting this long text that starts like this instead of the actual signatrue

By the way thank you for the feedback and helping so far! Really appreciate this.

".....
<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns="urn:schemas-microsoft-comofficeoffice"
xmlns:w="urn:schemas-microsoft-comoffice:word"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:m="http/schemas. microsoft...
xmlns= http www...

<head>
<meta http=Content-Type content="text/html; charset=windows-52......

 

[breynolds0431]

Unfortunately I keep getting this long text that starts like this instead of the actual signatrue

By the way thank you for the feedback and helping so far! Really appreciate this.

".....
<html xmlns:v="urn:schemas-microsoft-com:vml"
xmlns="urn:schemas-microsoft-comofficeoffice"
xmlns:w="urn:schemas-microsoft-comoffice:word"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:m="http/schemas. microsoft...
xmlns= http www...

<head>
<meta http=Content-Type content="text/html; charset=windows-52......

I was thinking maybe it was a 2016 and 365 difference. But, I just tried on my 365 version and it still puts the signature in the same path. Maybe there's some restriction from your IT or something else happening with your office's network.

Sorry we couldn't figure it out.

 

[godzilla185]

I was thinking maybe it was a 2016 and 365 difference. But, I just tried on my 365 version and it still puts the signature in the same path. Maybe there's some restriction from your IT or something else happening with your office's network.

Sorry we couldn't figure it out.

I appreciate your help! I will see if I can use your code suggestions as the base and then accomodate the emails within, rather than trying to accomodate the signature in my already prep'd code. Maybe switching it up or re-doing the code will help.

Thanks again