結論

本文將告訴您,一個 Visual Basic for Applications 巨集,此巨集使用 Microsoft Word 文件和 Microsoft Excel 活頁簿中的資料從 Microsoft Outlook 傳送郵件。

其他相關資訊

Microsoft 僅提供示範性的程式設計範例,不做任何明示或默示的保證。其中包括 (但不限於) 其適售性與適合某特定用途之默示擔保。本文將假設您已相當熟悉示範所使用的程式設計語言,以及用於建立和偵錯程序的工具。Microsoft 技術支援工程師可以協助說明特定程序的功能,但不會修改這些範例以提供附加功能或建構程序來滿足您的特定需求。

下列範例假設工作表中有兩個已定義的名稱:

  • 第一個已定義的名稱 "subjectcell" 參照到包含郵件主旨行 (例如 "This is a test message.") 的儲存格。

  • 第二個已定義的名稱 "tolist" 參照到包含收件者清單 (例如 "John Doe"、"Jane Doe"...等等) 之水平清單中的第一個儲存格。

您還必須有 Microsoft Word 文件。巨集會使用此文件的文字做為郵件訊息的郵件本文。

Sub SendOutlookMessages()

'Dimension variables.
Dim OL As Object, MailSendItem As Object
Dim W As Object
Dim MsgTxt As String, SendFile As String
Dim ToRangeCounter As Variant

'Identifies Word file to send
SendFile = Application.GetOpenFilename(Title:="Select MS Word " & _
"file to mail, then click 'Open'", buttontext:="Send", _
MultiSelect:=False)

'Starts Word session
Set W = GetObject(SendFile)

'Pulls text from file for message body
MsgTxt = W.Range(Start:=W.Paragraphs(1).Range.Start, _
End:=W.Paragraphs(W.Paragraphs.Count).Range.End)

'Ends Word session
Set W = Nothing

'Starts Outlook session
Set OL = CreateObject("Outlook.Application")
Set MailSendItem = OL.CreateItem(olMailItem)

ToRangeCounter = 0

'Identifies number of recipients for To list.
For Each xCell In ActiveSheet.Range(Range("tolist"), _
Range("tolist").End(xlToRight))
ToRangeCounter = ToRangeCounter + 1
Next xCell

If ToRangeCounter = 256 Then ToRangeCounter = 1

'Creates message
With MailSendItem
.Subject = ActiveSheet.Range("subjectcell").Text
.Body = MsgTxt

'Creates "To" list
For Each xRecipient In Range("tolist").Resize(1, ToRangeCounter)
RecipientList = RecipientList & ";" & xRecipient
Next xRecipient

.To = RecipientList
.Send
End With

'Ends Outlook session
Set OL = Nothing

End Sub

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×