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

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

kbay808Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
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
Kelvin SparksCommented:
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

kbay808Author Commented:
Thanks for the info
Nick67Commented:
@Scott
I haven't can-openered it that far, but it would be odd if an Outlook.Application object couldn't get at signatured
Kelvin SparksCommented:
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
Nick67Commented:
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!
Kelvin SparksCommented:
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
Nick67Commented:
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
Kelvin SparksCommented:
You can probably save as html within Access.

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

Gooduck
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.