vba 添加outlook 签名_VBA-Outlook签名档关联,找了好久才弄到的

Important read this :

The code on this page is only working with Outlook and not with Outlook Express or Windows Mail.

If you not use Outlook see the examples in the first section on my mail index page.

Copy the code in a Standard module, if you just started with VBA see this page.

http://www.rondebruin.nl/code.htm

Check out this page for Tips If you want to change the code on this page.

http://www.rondebruin.nl/mail/tips2.htm

Information

If you create a signature in Outlook it will save three files (HTM, TXT and RTF) into

SigString = "C:\Documents and Settings\" & Environ("username") & _

"\Application Data\Microsoft\Signatures\Mysig.txt"

In Vista or Win 7 use this

SigString = "C:\Users\" & Environ("username") & _

"\AppData\Roaming\Microsoft\Signatures\Mysig.txt"

Note: "Application Data" and "AppData" are hidden folders (Use Tools>Folder Options to change it)

In the two examples on this page we use the HTML and TXT file.

You must change the file name of the signature to your signature name in the code;

I use the name Mysig in the examples.

Important : This will not work if Word is your mail editor in Outlook 2000-2003, you can turn that of in the options in Outlook

Example 1 : Add signature within an plain message

Example 2 : Add HTML signature within an HTML message

Both examples use this Function

Function GetBoiler(ByVal sFile As String) As String

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

Example 1

This example add a signature to a mail with a small plain message.

Change the mail address and the name of the signature file in the code before you run it.

Sub Mail_Outlook_With_Signature_Plain()

' Don't forget to copy the function GetBoiler in the module.

' Working in Office 2000-2010

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")

Set OutMail = OutApp.CreateItem(0)

strbody = "Hi there" & vbNewLine & vbNewLine & _

"This is line 1" & vbNewLine & _

"This is line 2" & vbNewLine & _

"This is line 3" & vbNewLine & _

"This is line 4"

'Use the second SigString if you use Vista or Win 7 as operating system

SigString = "C:\Documents and Settings\" & Environ("username") & _

"\Application Data\Microsoft\Signatures\Mysig.txt"

'SigString = "C:\Users\" & Environ("username") & _

"\AppData\Roaming\Microsoft\Signatures\Mysig.txt"

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"

.Body = strbody & vbNewLine & vbNewLine & 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

Example 2

This example add a html signature to a html mail.

Change the mail address and the name of the signature file in the code before you run it.

Sub Mail_Outlook_With_Signature_Html()

' Don't forget to copy the function GetBoiler in the module.

' Working in Office 2000-2010

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")

Set OutMail = OutApp.CreateItem(0)

strbody = "

Dear Customer

" & _

"Please visit this website to download the new version.
" & _

"Let me know if you have problems.
" & _

"Ron's Excel Page" & _

"Thank you"

'Use the second SigString if you use Vista or win 7 as operating system

SigString = "C:\Documents and Settings\" & Environ("username") & _

"\Application Data\Microsoft\Signatures\Mysig.htm"

'SigString = "C:\Users\" & Environ("username") & _

"\AppData\Roaming\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 & "
" & 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

Early Binding

If you want to use the the Intellisense help showing you the properties and methods of the objects as you

type you can use Early binding. (bit faster but have problems when you distribute your workbooks)

See Dick's site for a explanation

http://www.dicks-clicks.com/excel/olBinding.htm

Add a reference to the Microsoft outlook Library

1) Go to the VBA editor, Alt -F11

2) Tools>References in the Menu bar

3) Place a Checkmark before Microsoft Outlook ? Object Library

is the Excel version number

Then replace this three lines in the code

Dim OutApp As Object

Dim OutMail As Object

Set OutMail = OutApp.CreateItem(0)

With this three

Dim OutApp As Outlook.Application

Dim OutMail As Outlook.MailItem

Set OutMail = OutApp.CreateItem(olMailItem)