Hi AllI have been working on a macro to generate a weekly set of emails I send out.
When I use the code below that I pieced together to insert my default signature, it works great until I add body text. Then the signature disappears.
Additionally, I am linking to a file on the server and am trying to figure out how to do so without the link being preceeded by "file:"
If I don't include it, the link is not clickable. When I try using <a href> tags, they simply show up in the text as plain text as well.
Sub MailFriday(dteDate As Date)
Dim OlApp As Outlook.Application
Dim ObjMail As Outlook.MailItem
Dim BodyHtml As String
Dim DirSig As String
Dim FileNameHTMSig As String
Dim Pos1 As Long
Dim Pos2 As Long
Dim SigHtm As String
DirSig = "C:\Users\" & Environ("username") & _
"\AppData\Roaming\Microsoft\Signatures"
FileNameHTMSig = Dir$(DirSig & "\*.htm")
' Code to handle there being no htm signature or there being more than one
SigHtm = GetBoiler(DirSig & "\" & FileNameHTMSig)
Pos1 = InStr(1, LCase(SigHtm), "<body")
' Code to handle there being no body
Pos2 = InStr(Pos1, LCase(SigHtm), ">")
' Code to handle there being no closing > for the body element
BodyHtml = "<table border=0 width=""100%"" style="" Color: #0000FF""" & _
" bgColor=#F0F0F0><tr><td align= ""center"">HTML table</td>" & _
"</tr></table><br>"
BodyHtml = Mid(SigHtm, 1, Pos2 + 1) & BodyHtml & Mid(SigHtm, Pos2 + 2)
Set OlApp = Outlook.Application
Set ObjMail = OlApp.CreateItem(olMailItem)
ObjMail.BodyFormat = olFormatHTML
ObjMail.Subject = "Appointments for " & _
Format(dteDate, "m/dd")
ObjMail.Body = "Hi All " & _
"—" & vbCrLf & "Following are the links to the Schedule Reports for the week of " & _
Format(dteDate, "m/dd") & _
":" & vbCrLf & vbCrLf & " file:\\corp\public\D\S-1-5-21-39997874\Crew%20and%20Sched%20Reports\All Boroughs" & _
"" & vbCrLf & vbCrLf & " Thanks,"
ObjMail.Recipients.Add "my@email.com"
ObjMail.Display
End Sub
When I use the code below that I pieced together to insert my default signature, it works great until I add body text. Then the signature disappears.
Additionally, I am linking to a file on the server and am trying to figure out how to do so without the link being preceeded by "file:"
If I don't include it, the link is not clickable. When I try using <a href> tags, they simply show up in the text as plain text as well.
Sub MailFriday(dteDate As Date)
Dim OlApp As Outlook.Application
Dim ObjMail As Outlook.MailItem
Dim BodyHtml As String
Dim DirSig As String
Dim FileNameHTMSig As String
Dim Pos1 As Long
Dim Pos2 As Long
Dim SigHtm As String
DirSig = "C:\Users\" & Environ("username") & _
"\AppData\Roaming\Microsoft\Signatures"
FileNameHTMSig = Dir$(DirSig & "\*.htm")
' Code to handle there being no htm signature or there being more than one
SigHtm = GetBoiler(DirSig & "\" & FileNameHTMSig)
Pos1 = InStr(1, LCase(SigHtm), "<body")
' Code to handle there being no body
Pos2 = InStr(Pos1, LCase(SigHtm), ">")
' Code to handle there being no closing > for the body element
BodyHtml = "<table border=0 width=""100%"" style="" Color: #0000FF""" & _
" bgColor=#F0F0F0><tr><td align= ""center"">HTML table</td>" & _
"</tr></table><br>"
BodyHtml = Mid(SigHtm, 1, Pos2 + 1) & BodyHtml & Mid(SigHtm, Pos2 + 2)
Set OlApp = Outlook.Application
Set ObjMail = OlApp.CreateItem(olMailItem)
ObjMail.BodyFormat = olFormatHTML
ObjMail.Subject = "Appointments for " & _
Format(dteDate, "m/dd")
ObjMail.Body = "Hi All " & _
"—" & vbCrLf & "Following are the links to the Schedule Reports for the week of " & _
Format(dteDate, "m/dd") & _
":" & vbCrLf & vbCrLf & " file:\\corp\public\D\S-1-5-21-39997874\Crew%20and%20Sched%20Reports\All Boroughs" & _
"" & vbCrLf & vbCrLf & " Thanks,"
ObjMail.Recipients.Add "my@email.com"
ObjMail.Display
End Sub