We help IT Professionals succeed at work.

How do I add the signature block to my MS Access vba code?

kbay808
kbay808 asked
on
The below code works perfect, but it’s missing the users signature block.  How do I add it?

Option Compare Database

Function Copy_Of_DailyReport1()
On Error GoTo Copy_Of_DailyReport1_Err

    DoCmd.SetWarnings False
    DoCmd.OpenForm "frmFOL", acNormal, "", "", , acNormal
    DoCmd.OpenForm "frmFSL", acNormal, "", "", , acNormal
    DoCmd.SendObject acReport, "rptDailyReport", "XPSFormat(*.xps)", Forms!frmFOL![FOL Email] & "; " & Forms!frmFSL![FSL Email], "FSQM@nmci-isf.com", "", "Daily Audit", "Here are the results for today's audit.  Please let me know if you have any questions.", True, ""
    DoCmd.Close acForm, "frmFOL"
    DoCmd.Close acForm, "frmFSL"


Copy_Of_DailyReport1_Exit:
    Exit Function

Copy_Of_DailyReport1_Err:
    MsgBox Error$
    Resume Copy_Of_DailyReport1_Exit

End Function

Open in new window

Comment
Watch Question

Most Valuable Expert 2014
Commented:
You are close to being in the weeds.
Outlook is only being invoked in a tangential way here.
There is no way to get the Outlook signature by using SendObject

If you spun up a full Outlook automation to create and send the message, you could probably get the signature in their on a per-user basis.  There's the final option of SendObject -- the TemplateFile path -- but you really have no way to customize that on a per-user basis
By default signature blocks are not added - and there's no setting that you can change to make it add.

The only way I have found to do this (and I no longer have to code) is to open outlook in code and create your email there. You must not use the body object of the email but choose and xml body and you have to go to the file system, and find the saved signature block and manually add it in the correct place.

Kelvin
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Here's an article on using Outlook Automation to handle your emails from Access:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_4316-Automate-Outlook-in-VBA-with-the-OutlookCreateItem-Class.html

Still doesn't tell you how to add the Signature (since you can't do it, far as I can tell).

Author

Commented:
Thanks for the info
Most Valuable Expert 2014

Commented:
@Scott
I haven't can-openered it that far, but it would be odd if an Outlook.Application object couldn't get at signatured
The code you need to extract the signature is

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)

where sFile is the fielname of the signature

You can then insert that into the email and append to HTMLBody ( the rest of the boy of your email.


Kelvin
Most Valuable Expert 2014

Commented:
where sFile is the filename of the signature
That's a bit of a rub
Because everyone may have one, named different and perhaps in different locations
So how do you find the sfile for a particular Outlook user?

Turns out, you don't have to.
If there's a default signature, the newly opened MailItem will have the signature in its .HTMLBody property already IF YOU FORCE IT TO DISPLAY with .Display
Capture that to a string
Compose your HTMLBody and append what you captured to the end.
Neat!
They're generally in the same location (determined by Outlook) - you need the html version - and each user is likely to only have one. So you should be able to find it

OK, that will be a change from the version I originally used where it did not appear - and was documented as such - 2003 or 2007 - can't remember which
Most Valuable Expert 2014

Commented:
They appear to be at
C:\Users\<UserName>\AppData\Roaming\Microsoft\Signatures
There will be a .htm, .rtf and .txt version there

Now, what if the users has more than one signature defined!
Which one to use?

Still the TextStream idea is good.
I use that when sending Excel files
I'll open Excel and save the file as HTML
I'll textstream the HTML into HTMLBody and then discard the HTML file
You can probably save as html within Access.

Kelvin
In terms of multiple files, then you have to develop some rule to manage!

Gooduck