Solved: Outlook & Access

[winxmun]

Hi there, attached with my coding & wondering anybody can assist on the following. ie, at the body section, i'm thinking whether can auto pull the individual Signature in Ms Outlook? or how to input "enter" in the coding.
eg. in the body, how do i input the following (in the box) in order to appear the same in the Outlook?

"Thanks & regards
winxmun
cheers!!!"


Private Sub SendEmail_Click()

Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem

On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then Set olApp = New Outlook.Application
On Error GoTo 0

Set olMail = olApp.CreateItem(olMailItem)
With olMail
.Subject = "Testing"
.Recipients.Add "winxmun@yahoo.com"
.Attachments.Add "d:\testing.xls"
.Body = "winxmun; Thanks & Regards"
.Display 'This will display the message for you to check and send yourself subsequently
'.Send ' This will send the message straight away

End With

End Sub



thank you very much

[Oorang]

As far as I am aware, the Outlook object model does not provide access to signatures. However as you may be aware, the signatures are stored in txt and/or html files in C:\Documents and Settings\USERNAME\Application Data\Microsoft\Signatures. So you can use the file system object to retrive the contents of the sig files. Ron DeBruin has a pretty good write up on the issue here (http://www.rondebruin.nl/mail/folder3/signature.htm). The one thing he does not address is how to get the sig file if you don't know the file name. A simple way to address the issue is just take the first one you find. You can do that like so:
Public Sub Test()
MsgBox GetSigPath("html", True) & vbNewLine & "Or" & vbNewLine & GetSigPath("html", False)
End Sub
Public Function GetSigPath(extension As String, Optional getShortName As Boolean) As String
Const strSigFolder_c As String = "C:\Documents and Settings\xxx\Application Data\Microsoft\Signatures"
Const strTarget_c As String = "xxx"
Const strUserName_c As String = "USERNAME"
Dim fso As Scripting.FileSystemObject
Dim fldrSig As Scripting.Folder
Dim fl As Scripting.File
Dim strFolder As String
Dim lngExtLen As Long
lngExtLen = VBA.Len(extension)
strFolder = VBA.Replace(strSigFolder_c, strTarget_c, VBA.Environ$(strUserName_c))
Set fso = New Scripting.FileSystemObject
Set fldrSig = fso.GetFolder(strFolder)
For Each fl In fldrSig.Files
If VBA.StrComp(Right$(fl.Name, lngExtLen), extension, vbTextCompare) Then
Exit For
End If
Next
If getShortName Then
GetSigPath = fso.BuildPath(fl.ShortPath, fl.ShortName)
Else
GetSigPath = fso.BuildPath(fl.Path, fl.Name)
End If
End Function

[winxmun]

Hi Oorang,

I've tried Ron DeBruin's method for my 2nd option, it works! However i've not try your method to obtain the signature from Ms Outlook. Like you've mentioned, i hv few user running this code. Not sure how to tell the system to pick from the right Signature. I will try later.

Anyway, tks a lot!!! :thumb