• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2361
  • Last Modified:

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
0
MrLogin
Asked:
MrLogin
  • 6
  • 6
1 Solution
 
weellioCommented:
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
 
weellioCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
MrLoginAuthor Commented:
In Outlook checked Tools | Options | Mail Format - "select signatuers for account" is set to Microsoft Exchange Server for all users.
0
 
weellioCommented:
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
 
weellioCommented:
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
 
weellioCommented:
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
 
weellioCommented:
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now