VBA-Outlook签名档关联,找了好久才弄到的

 

http://www./mail/folder3/signature.htm

 

Summary一下

Sub 抓签名()

    Dim SigString As String
    Dim Signature As String
    SigString = "C:\Documents and Settings\" & Environ("username") & _
                "\Application Data\Microsoft\Signatures\Mysig.txt"

    'Viso 或win7操作系统用下面的地址
    'SigString = "C:\Users\" & Environ("username") & _
     "\AppData\Roaming\Microsoft\Signatures\Mysig.txt"
    If Dir(SigString) <> "" Then
        Signature = GetBoiler(SigString)
    Else
        Signature = ""
    End If
'已得到签名档的内容，关联到邮件的body上去就行了

End Sub

'下面的子程序是少不了的，安不安全真不知道

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

 

 

------------------------------------------------------------------------------------------------------------------------------

Insert Outlook Signature in mail

Ron de Bruin (last updated 5-Aug-2010)
Go back to the Mail index page

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./code.htm

Check out this page for Tips If you want to change the code on this page.
http://www./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 = "[e-mail]"
.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 = "<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./tips.htm"">Ron's Excel Page</A>" & _
"<br><br><B>Thank you</B>"
'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 = "[e-mail]"
.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

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./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)