Send Email From Excel Using VBA And VBScript - Tutorial

Trang chủ » Visual Basic Code To Send Email From Excel » Send Email From Excel Using VBA And VBScript - Tutorial

Có thể bạn quan tâm

[Excel + VBA - Send Email - Example]¶

After the reference is added, click ThisWorkBook and input the following codes:

Please enable Macro if you closed and re-opened this file, otherwise the codes cannot be executed.

excel enable macro

Important

You need to access the Trust Center in the Excel Options dialog box. Click the Microsoft Office Button, and then click Excel Options. In the Trust Center category, click Trust Center Settings, and then click the Macro Settings category.

' To use the following codes, please download and install ' https://www.emailarchitect.net/webapp/download/easendmail.exe on your machine Option Explicit Private WithEvents oSmtp As EASendMailObjLib.Mail Private CurrentEmailIsFinished As Boolean Private HasErrorWithEmail As Boolean Private ErrorDescription As String Private Sub InitVariables() CurrentEmailIsFinished = True HasErrorWithEmail = False ErrorDescription = "" End Sub Private Sub oSmtp_OnAuthenticated() Application.StatusBar = "Authenticated" End Sub Private Sub oSmtp_OnClosed() CurrentEmailIsFinished = True End Sub Private Sub oSmtp_OnConnected() Application.StatusBar = "Connected" End Sub Private Sub oSmtp_OnError(ByVal lError As Long, ByVal ErrDescription As String) HasErrorWithEmail = True CurrentEmailIsFinished = True ErrorDescription = ErrDescription End Sub Private Sub oSmtp_OnSending(ByVal lSent As Long, ByVal lTotal As Long) Application.StatusBar = "Sending " & lSent & "/" & lTotal & " ..." End Sub Public Sub SendMailFromWorkBook() Dim oSheet Set oSheet = ThisWorkbook.Sheets(1) Dim i, rows rows = oSheet.UsedRange.rows.Count Dim sender, name, address, subject, bodyTemplate, body, bodyFormat bodyFormat = 0 'Text body format ' Please change sender address to yours sender = "test@emailarchitect.net" subject = "Test email from Excel and VBA" ' Use a body template to build body text based on recipient's name bodyTemplate = "Dear {name}," & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _ "This is a test email from Excel using VBA, do not reply." Dim emailSent emailSent = 0 ' enumerate all email addresses in sheet1 For i = 2 To rows name = Trim(oSheet.Cells(i, 1)) address = Trim(oSheet.Cells(i, 2)) body = Replace(bodyTemplate, "{name}", name) If Not SendMailTo(sender, name, address, subject, body, bodyFormat) Then Exit Sub End If emailSent = emailSent + 1 Next Application.StatusBar = "Total " & emailSent & " email(s) sent." End Sub Function SendMailTo(sender, name, address, subject, body, bodyFormat) Set oSmtp = New EASendMailObjLib.Mail oSmtp.LicenseCode = "TryIt" ' Please change server address, user, password to yours oSmtp.ServerAddr = "mail.emailarchitect.net" oSmtp.UserName = "test@emailarchitect.net" oSmtp.Password = "yourpassword" ' Set server port, if 25 port doesn't work, try to use 587 port oSmtp.ServerPort = 25 ' Using TryTLS, ' If smtp server supports TLS, then TLS connection is used; otherwise, normal TCP connection is used. ' https://www.emailarchitect.net/easendmail/sdk/?ct=connecttype oSmtp.ConnectType = 4 ' If your server is Exchange 2007 or later version, you can use EWS protocol. ' https://www.emailarchitect.net/easendmail/sdk/?ct=protocol ' Set Exchange Web Service Protocol - EWS - Exchange 2007/2010/2013/2016 ' oSmtp.Protocol = 1 oSmtp.FromAddr = sender oSmtp.AddRecipient name, address, 0 oSmtp.subject = subject oSmtp.bodyFormat = bodyFormat oSmtp.BodyText = body ' You can add attachment like this: ' Add attachment from local disk ' If oSmtp.AddAttachment("d:\test.jpg") <> 0 Then ' Application.StatusBar = "Failed to add attachment with error:" & oSmtp.GetLastErrDescription() ' SendMailTo = False ' Exit Function 'End If Application.DisplayStatusBar = True Application.StatusBar = "Connecting " & oSmtp.ServerAddr & " ..." oSmtp.Asynchronous = 1 InitVariables CurrentEmailIsFinished = False oSmtp.SendMail Do While Not CurrentEmailIsFinished ' Wait for the email sending, you can do other thing here DoEvents Loop If HasErrorWithEmail Then Application.StatusBar = "Failed to send email to " & address & "; " & ErrorDescription SendMailTo = False Else Application.StatusBar = "Message to " & address & " has been submitted to server." SendMailTo = True End If Set oSmtp = Nothing End Function

and click Module1 and change the codes to:

ThisWorkbook.SendMailFromWorkBook

Close VBA IDE and back to Excel, press Alt+F8 to open Macro dialog box, select SendMail and click Run.

excel run macro

You will see the status and result at Excel status bar.

excel status bar

Từ khóa » Visual Basic Code To Send Email From Excel