I have a excel sheet with personal information such as name, email address etc. and I also have a VBA code that when a cell in a specific range is selected (range R in this case) then call the VBA macro to send a mail.
但是如何將特定人員的電子郵件地址分配給我的 VBA 代碼？
例如：如果我單擊單元格 R5，則 VBA 宏應該開始運行以將郵件發送到單元格 M5 和單元格 O5 中的電子郵件地址，或者如果我單擊單元格 R10，那么它應該通過電子郵件發送到單元格 M10 中的電子郵件地址和細胞 O10。
請參閱下面我到目前為止的代碼：
當我單擊 R 范圍內的任何單元格時，將觸發以下 VBA 宏
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("R6:R1000000")) Is Nothing Then
Call Send_Email
End If
End If
End Sub
宏 Send_Email：
Sub Send_Email()
Dim EmailApp As Outlook.Application
Dim NewEmailItem As Outlook.MailItem
Dim Scr As String
Set EmailApp = New Outlook.Application
Set NewEmailItem = EmailApp.CreateItem(olMailItem)
NewEmailItem.To = ****** here should be the cell reference ******
'NewEmailItem.CC = ****** here should be the cell reference ******
NewEmailItem.Subject = "abcd"
With NewEmailItem
.HTMLBody = "Hello abcd" etc.
End With
End Sub
uj5u.com熱心網友回復：
這是完全相同的示例代碼 - 顯示如何根據存盤在作業簿中的資料向收件人串列發送電子郵件。收件人電子郵件地址必須在 A 列中，并且電子郵件的正文必須在活動作業表的第一個文本框中：
Sub Sample()
'Setting up the Excel variables.
Dim olApp As Object
Dim olMailItm As Object
Dim iCounter As Integer
Dim Dest As Variant
Dim SDest As String
'Create the Outlook application and the empty email.
Set olApp = CreateObject("Outlook.Application")
Set olMailItm = olApp.CreateItem(0)
'Using the email, add multiple recipients, using a list of addresses in column A.
With olMailItm
SDest = ""
For iCounter = 1 To WorksheetFunction.CountA(Columns(1))
If SDest = "" Then
SDest = Cells(iCounter, 1).Value
Else
SDest = SDest & ";" & Cells(iCounter, 1).Value
End If
Next iCounter
'Do additional formatting on the BCC and Subject lines, add the body text from the spreadsheet, and send.
.BCC = SDest
.Subject = "FYI"
.Body = ActiveSheet.TextBoxes(1).Text
.Send
End With
'Clean up the Outlook application.
Set olMailItm = Nothing
Set olApp = Nothing
End Sub
在您使用單獨的發送電子郵件功能的場景中，您可以通過引數傳遞所需的資料。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/461057.html
上一篇：使用帶有附件的Microsoftgraph發送電子郵件。微軟代碼示例不清楚
下一篇：Java調度程式到郵件