Outlook Signature Insertion Using Excel VBA

[Anne Troy]

Got the code from Ron de Bruin, but can't seem to make it work. The whole thing was working good so far, though I am not really done with the body. I added the signature in every possible way, but it simply doesn't put my signature in. Yes, the file exists in the path given. He used the "environ" string, but I hard-coded it just to see if that was part of the problem, and it changed nothing.

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


Sub TestFile()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Dim strbody As String
    Dim SigString As String
    Dim Signature As String


    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")

    On Error GoTo cleanup
    For Each cell In Rows("3").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" Then
       ' And _
        '   LCase(Cells(cell.Row, "C").Value) = "yes"
        

            Set OutMail = OutApp.CreateItem(0)
            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = "POS Order Update: " & Range("D7").Text & " " & Range("E7").Text
                .Body = "Hi, " & Range("c7").Text & "!" & Chr(10) & Chr(10) & _
                Range("D10").Text & " has been assigned to program your Point of Sale database."
                
                .Display  'Or use Send
                SigString = "C:\Users\atroy\AppData\Roaming\Microsoft\Signatures\AnneTroyHT.htm"

End With
            On Error GoTo 0

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

  Set OutMail = Nothing
        End If
    Next cell

cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True


End Sub

Help!

 

[Anne Troy]

I just realized I'd already tried to sort this issue right here in this string, and earlier today, I posted this question again:
http://www.mrexcel.com/forum/showthread.php?t=566070

But my code has changed significantly, and I cannot use a template.

 

[viktoryeung]

I figured out how to find the filepath of your signature in Outlook.
Options/Mail/and press Ctrl button as you click the "Signatures..." button

http://blogs.office.com/b/microsoft-outlook/archive/2008/07/11/how-do-i-back-up-my-signatures.aspx

 

[Laura Graham]

Can you please help me, i have the following code and need to add a specific signature called "Avon" or in C: drive under signature as Avon.rtf, it only picks up the default signature, and i know nothing about macros if someone can insert the code in the correct place for me that would help a lot, thank you

Sub Emailtoclient()

strPath = Environ$("temp") & "\"

strFName = ActiveSheet.Range("I12")
strFName = Range("J5") & "-" & ActiveSheet.Range("H12") & ".pdf"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
strPath & strFName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strbody As String
Dim wddoc As Object
Dim olInsp As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)

On Error Resume Next
With OutMail
.BodyFormat = 1
.Display
.To = Range("H14").Value
.CC = ""
.BCC = ""
.Subject = "Avon invoice" & "-" & Range("J5")

Set olInsp = .GetInspector
Set wddoc = olInsp.WordEditor

wddoc.Range.InsertBefore "Good Day" & " " & Range("H12") & "," & vbCr & vbCr & "Thank you for your order in brochure" & " " & Range("J8") & vbCr & "Please find attached herewith your invoice" & "-" & Range("J5") & vbCr & vbCr
.Attachments.Add strPath & strFName
.SendUsingAccount = OutApp.Session.Accounts.Item(3)


End With

Kill strPath & strFName
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

 

[adavid]

In the .htm file in the signatures directory you can edit the htm file. The pictures are stored as relative path and when you use the code it looses that path so if you use discrete path it will be able to find the pictures. so go into the file and look for any relative paths and make them discrete.

"/Microsoft/Signatures/picturefile.jpg"

change that to include the whole path

"/root/user/blah blah../Microsoft/Signatures/picturefile.jpg"

This solved the missing image problem for me.

I figured out how to find the filepath of your signature in Outlook.
Options/Mail/and press Ctrl button as you click the "Signatures..." button

http://blogs.office.com/b/microsoft-outlook/archive/2008/07/11/how-do-i-back-up-my-signatures.aspx