我打开了我的“MASTER.xlsm”工作簿。我从“MASTER.xlsm”运行代码。
我想从我的“MASTER.xlsm”获取值到新创建的 Outlook 电子邮件。我不会让他们到那里。
代码中的序列有问题吗?
Sub EmailWithOutlook()
Dim oApp As Object
Dim oMail As Object
Dim WB As Workbook
Dim WBBW As Workbook
Dim FileName As String
Dim wSht As Worksheet
Dim shtName As String
Set WBBW = Workbooks("MASTER.xlsm")
WBBW.Worksheets("MAIN").Range("D134").Value = variableX
WBBW.Worksheets("MAIN").Range("D11").Value = variableY
WBBW.Worksheets("MAIN").Range("D13").Value = variableZ
Application.ScreenUpdating = False
' Make a copy of the active worksheet
' and save it to a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = "My file"
On Error Resume Next
Kill "\" & FileName
On Error GoTo 0
WB.SaveAs FileName:=Environ$("temp") & "\" & FileName
'Create and show the Outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'Uncomment the line below to hard code a recipient
.To = ""
'Uncomment the line below to hard code a subject
.Subject = "My subject | " & variableX & " | " & variableY
'Uncomment the lines below to hard code a body
.HTMLBody = "<BODY style=font-size:11pt;font-family:Calibri>Dear Sir/Madam, <br><br> please check this " & _
variableZ & "" & _
" and comment if needed.<br> Waiting for your reply ASAP. <br><br> Thank you!</BODY>" & .HTMLBody
.Attachments.Add WB.FullName
.Display
End With
'Delete the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False
'Restore screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub
我检查了你的代码,正如尼顿所说,你应该首先声明变量,例如“variableX”“variableY”“variableZ”。但是,据我所知,您只想从 Excel 文件发送电子邮件。所以可以参考如下代码:
Sub test()
Dim strReportName As String
Dim oLook As Object
Dim oMail As Object
Dim olns As Outlook.Namespace
Dim strTO As String
Dim strCC As String
Dim strMessageBody As String
Dim strSubject As String
Set oLook = CreateObject("Outlook.Application")
'Set olns = oLook.GetNamespace("MAPI")
Set oMail = oLook.CreateItem(0)
'*********************** USER DEFINED SECTION ************************
strTO = "[email protected]"
strMessageBody = "<---This is an automatically generated email. Please do not respond.---->"
strSubject = "Daily Skip"
'*********************************************************************
With oMail
.To = strTO
.CC = strCC
.Body = strMessageBody
.Subject = strSubject
'.Attachments.Add "C:\Output Reports\SkipLotReport.xlsx"
.Display
End With
Set oMail = Nothing
Set oLook = Nothing
'Set olns = Nothing
'DB.Close
'tbloutput.Close
'dbLocal.Close
objWorkbook.Close
'Set objmail = Nothing
'Set DB = Nothing
Set tbloutput = Nothing
Set objWorksheet = Nothing
Set objWorkbook = Nothing
Set objExcel = Nothing
Set tbloutput = Nothing
Set dbLocal = Nothing
End Sub
注意:请参考 Outlook 库。
有关更多信息,请参阅链接:使用 VBA 向多个收件人发送电子邮件
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句