Send Email From Access Using VBScript And Email Queue

Trang chủ » Visual Basic Script Send Email With Attachment » Send Email From Access Using VBScript And Email Queue

Có thể bạn quan tâm

Create VBScript file¶

After the installation is completed, you can create a VBScript by your favorite text editor. I prefer to use Visual Code or Notepad. Create a file name SendEmailFromAccess.vbs, input the following codes:

Option Explicit ' If you get error with connecting database (drive not found) ' Please install https://www.microsoft.com/en-us/download/details.aspx?id=13255 on your machine ' Dim dbConnection dbConnection = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" & GetScriptDirectory() & "\Sample.accdb;Uid=;Pwd=;" ' To use the following codes, please download and install ' https://www.emailarchitect.net/webapp/download/easendmail.exe on your machine SendHtmlMailFromAccess dbConnection Function GetScriptDirectory() GetScriptDirectory = Left(WScript.ScriptFullName, InStr(WScript.ScriptFullName, WScript.ScriptName) - 2) End Function Sub SendHtmlMailFromAccess(dbConnection) WScript.Echo("Open " & dbConnection & " ...") Dim conn, rs, sql Set conn = CreateObject("ADODB.Connection") conn.Open dbConnection Set rs = CreateObject("ADODB.Recordset") sql = "select * from users" rs.Open sql, conn rs.MoveFirst Dim sender, name, address, subject, bodyTemplate, body, bodyFormat, attachment bodyFormat = 1 'HTML body format attachment = "" ' you can specify a file to be attached ' Please change sender address to yours sender = "[email protected]" subject = "Test email from MS Access and VBScript" ' Use a body template to build body text based on current workbook bodyTemplate = BuildHtmlBody(rs) Dim emailSent emailSent = 0 Do While Not rs.EOF name = Trim(rs("Name")) address = Trim(rs("Email")) body = Replace(bodyTemplate, "{name}", name) If Not SendMailTo(sender, name, address, subject, body, bodyFormat, attachment) Then Exit Sub End If emailSent = emailSent + 1 rs.MoveNext Loop WScript.Echo("Total " & emailSent & " email(s) sent.") End Sub Function BuildHtmlBody(rs) Dim html, name, address, age, department html = "<!DOCTYPE html><html><body>" html = html & "<div style=""font-family:'Segoe UI', Calibri, Arial, Helvetica; font-size: 14px; max-width: 768px;"">" html = html & "Dear {name}, <br /><br />This is a test email from MS Access using VBScript. <br />" html = html & "Here is your data:<br /><br />" html = html & "<table style='border-spacing: 0px; border-style: solid; border-color: #ccc; border-width: 0 0 1px 1px;'>" name = Trim(rs("Name")) address = Trim(rs("Email")) age = Trim(rs("Age")) department = Trim(rs("Department")) html = html & "<tr>" html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & name & "</td>" html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & address & "</td>" html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & age & "</td>" html = html & "<td style='padding: 10px; border-style: solid; border-color: #ccc; border-width: 1px 1px 0 0;'>" & department & "</td>" html = html & "</tr>" html = html & "</table></div></body></html>" BuildHtmlBody = html End Function Function SendMailTo(sender, name, address, subject, body, bodyFormat, attachment) Dim oSmtp Set oSmtp = CreateObject("EASendMailObj.Mail") oSmtp.LicenseCode = "TryIt" ' Please change server address, user, password to yours oSmtp.ServerAddr = "mail.emailarchitect.net" oSmtp.UserName = "[email protected]" 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 oSmtp.FromAddr = sender oSmtp.AddRecipient name, address, 0 oSmtp.subject = subject oSmtp.bodyFormat = bodyFormat oSmtp.BodyText = body ' Add attachment from local disk If attachment <> "" And oSmtp.AddAttachment(attachment) <> 0 Then WScript.Echo("Failed to add attachment with error:" & oSmtp.GetLastErrDescription()) SendMailTo = False Exit Function End If WScript.Echo("Connecting " & oSmtp.ServerAddr & " ...") If oSmtp.SendMail() <> 0 Then WScript.Echo("Failed to send email to " & address & "; " & oSmtp.GetLastErrDescription()) SendMailTo = False Else WScript.Echo("Message to " & address & " has been submitted to server.") SendMailTo = True End If Set oSmtp = Nothing End Function

Từ khóa » Visual Basic Script Send Email With Attachment