Include signature when creating new e-mail from VBA command

Hi folks,

When I create a new Outlook 2003 e-mail via VBA from an Access 2003 application I would like to include my default signature (MS Word editor).    If I create a new e-mail manually from within Outlook the signature is present (e.g. can right-click on it and select an alternative one if required), but not from the Access VBA code snippet below:

  Set MyMail = MyOutlook.CreateItem(olMailItem)
  MyMail.Recipients.Add (sUserName)
  MyMail.Recipients.ResolveAll
  MyMail.Subject = "Subject line"
  MyMail.Body = "Message data"
  MyMail.Display

MSDN offers copious advice on including a digital signature but I need the human readable one.

Thanks
LVL 1
MrLoginAsked:
Who is Participating?
 
William ElliottConnect With a Mentor Sr Tech GuruCommented:
Sub Mail_Outlook_With_Signature_Html()
' Don't forget to copy the function GetBoiler in the module.
' Working in Office 2000-2007
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Dim SigString As String
    Dim Signature As String
 
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
 
    strbody = "<H3><B>Dear Customer</B></H3>" & _
              "Please visit this website to download the new version.<br>" & _
              "Let me know if you have problems.<br>" & _
              "<A HREF=""http://www.rondebruin.nl/"">Ron's Excel Page</A>" & _
              "<br><br><B>Thank you</B>"
 
    SigString = "C:\Documents and Settings\" & Environ("username") & _
                "\Application Data\Microsoft\Signatures\Mysig.htm"
 
    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If
 
    On Error Resume Next
    With OutMail
        .To = "ron@debruin.nl"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .HTMLBody = strbody & "<br><br>" & Signature
        'You can add files also like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
0
 
William ElliottSr Tech GuruCommented:
try adding this, then you can just have the signature in a text file and read the text file into your email.....


Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String


If BodyFile$ = "" Then
    MsgBox "No body, no message." & vbNewLine & vbNewLine & _
         "Quitting...", vbCritical, "I Ain't Got No Body!"
    Exit Function
End If

 ' Check to make sure the file exists...
If fso.FileExists(BodyFile$) = False Then
    MsgBox "The body file isn't where you say it is. " & vbNewLine & vbNewLine & _
           "Quitting...", vbCritical, "I Ain't Got No-Body!"
    Exit Function
End If
    Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
    MyBodyText = MyBody.ReadAll
    MyBody.Close



or go here and solve all other issues
http://www.jephens.com/howtoemail.asp
0
 
MrLoginAuthor Commented:
Neat idea - however should have specified I need to use the application to create e-mails on desktops across the business where each user has their own Outlook embedded signature.    It may be possible to identify the default signature file (if active) for the currently logged on user (could be anyone in the business), would need the filename and path.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
MrLoginAuthor Commented:
In Outlook checked Tools | Options | Mail Format - "select signatuers for account" is set to Microsoft Exchange Server for all users.
0
 
William ElliottSr Tech GuruCommented:
did that make it do what you wanted it to do?


the location of the users signatue file is
C:\Documents and Settings\<username>\Application Data\Microsoft\Signatures\<signature>.htm


usage =
getboiler(C:\Documents and Settings\<username>\Application Data\Microsoft\Signatures\<signature>.htm
)


from here http://www.rondebruin.nl/mail/folder3/signature.htm
Important : This will not work if Word is your mail editor, you can turn that of in the options in Outlook
Function GetBoiler(ByVal sFile As String) As String
'Dick 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
 
0
 
MrLoginAuthor Commented:
Found the signatures here -

C:\userdata\<name>\EAMProfile\Microsoft\Signatures\

There are 3 signature files on my PC, so the question is how to determine the correct one to use given that in general users may also have multiple signature files on their PCs?
0
 
William ElliottSr Tech GuruCommented:
HKCU\software\microsoft\office\<version>\common\mailsettings\signatures\newsignature

this is where you would get the name of the signature file.
append htm to the end then look in a series of locations. and if the file exists, then use it as the signature, if not, then don't add the signature,.
0
 
William ElliottSr Tech GuruCommented:
any luck?
0
 
MrLoginAuthor Commented:
No, the configuration at work doesn't tie up with this - the registry keys are present but empty.   When I am back at work next week will try an heuristic approach - e.g. load the signature with the most recent file timestamp (unless someone can offer a more rigorous or alternative approach in the mean time).   Slight pause ...
0
 
William ElliottSr Tech GuruCommented:
why not create a generic signature file and insert the users infrmatino into it by gather it from AD?

for example  save this as a vbd

On Error Resume Next
Dim objSysInfo, objUser
Set objSysInfo = CreateObject("ADSystemInfo")

' Currently logged in User
Set objUser = GetObject("LDAP://" & objSysInfo.UserName)


results = objUser.givenName & " " & objUser.sn & vbcrlf & _
                  objUser.company & vbcrlf & vbcrlf & _
                  objUser.mail & vbcrlf & _
                  objUser.telephoneNumber  & vbcrlf  & vbcrlf & _
                  objUser.streetAddress  & vbcrlf & _      
                  objUser.l & ", " & objUser.st & " " & objUser.postalCode  & vbcrlf
wscript.echo results
0
 
MrLoginAuthor Commented:
Our infrastructure people are due to roll out a desktop refresh including AD in the next 6 months.    At the moment AD is restricted to some domains.   Appreciate the flow of good ideas, but the platform is limited.  As noted will revisit the issue on Monday.
0
 
MrLoginAuthor Commented:
Thanks Weellio ... appreciate the ideas you've advanced.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.