Hi, I have a table in my Access 2007 database that has name and email_address fileds. I want to send to all the email addresses in the table. From the examples I have found, the way to connect is using this sort of code:

oSmtp.AddHeader "X-Data-Connection", _
'"Driver={Microsoft Access Driver (*.mdb)};Dbq={$var_easendmailpath}\easendmail_demo.mdb;Uid=;Pwd=;"

I am not sure what the correct syntax should be to connect to a local database as I am running the code from access by clicking on a button on an access form.

You are using EASendMail Service to send the email, so you still need to specify the database path, because EASendMail Service is an external service, even you call SendMailToQueue in access vba, you still need to specify the path.

For example:

oSmtp.AddHeader "X-Data-Connection", _
'"Driver={Microsoft Access Driver (*.mdb)};Dbq=d:\myfolder\my.mdb;Uid=;Pwd=;"

Thank you so much for your help Ivan. This is the code I am using:

oSmtp.AddHeader "X-Data-Connection", _
Driver = "{Microsoft Access Driver (*.accdb)};Dbq=C:\myfolder\mydatabse.accdb;Uid=;Pwd=;"

I am getting the following error:

The system cannot find the file specified.
NativeErrorCode: 2[SendMailToQueEx]

any ideas what I might be doing wrong?

please download easendmail service and install it on your machine.

http://www.emailarchitect.net/webapp/download/easendmailservice.exe 

Thank You Ivan

I did install and now it works in that it tells me "email was sent successfully", but mails dont come through. Do I need to set smtp settings in the EASendMail service manager or can you just do it with code?

My table is "EmailTest" and it has two fields, "user_name" and "email" This is the code I have:

Dim oSmtp As New EASendMailObjLib.Mail
oSmtp.LicenseCode = "TryIt"

' Set your sender email address
oSmtp.FromAddr = "***@marcrausch.com"

' Set email subject
oSmtp.Subject = "simple email from VB 6.0 project"

' Your SMTP server address
oSmtp.ServerAddr = "mail.marcrausch.com"

' User and password for ESMTP authentication, if your server doesn't require
' User authentication, please remove the following codes.
oSmtp.UserName = "***@marcrausch.com"
oSmtp.Password = "****"

' If your smtp server requires SSL connection, please add this line
' oSmtp.SSL_init

' If you want to EASendMail service send the email after 10 minutes, please use the following code.
' oSmtp.Date = DateAdd("n", 10, Now())

' EASendMail will use the following connection to connect to the database,
' the syntax is same as ADO connection object.

oSmtp.AddHeader "X-Data-Connection", _
Driver = "{Microsoft Access Driver (*.accdb)};Dbq=C:\Users\Public\TestDatabase.accdb;Uid=;Pwd=;"


' EASendMail will select the fields by the following sql statement
' before sending email,
' then pick the recipient address from specified field.
oSmtp.AddHeader "X-Sql-Select", "SELECT * FROM EmailTest"

' Pick "name" field as the recipient name and "address" field as the recipient address.
' You can also use {$var_srecord:fieldname} to pick any field in X-Sql-Select statement
' and put it to subject, bodytext, then EASendMail will replace it automatially.
oSmtp.DisplayTo = """{$var_srecord:user_name}"" <{$var_srecord:email}>"
oSmtp.AddHeader "X-Rcpt-To", "{$var_srecord:email}"

' EASendMail service will execute the following sql statement on
' every email was sent successfully.
oSmtp.AddHeader "X-Sql-OnSentSuccess", _
"INSERT INTO sentlog ( server, email ) VALUES( '{$var_server}', '{$var_rcptaddr}' )"

' EASendMail service will execute the following sql statement on
' every email could not be sent.
oSmtp.AddHeader "X-Sql-OnSentError", _
"INSERT INTO errorlog( email, server, errorcode, errordescription ) " & _
"VALUES( '{$var_rcptaddr}', '{$var_server}', '{$var_errcode}', '{$var_errdesc}' )"

Dim bodytext As String

bodytext = "Hi {$var_srecord:user_name}, " & Chr(13) & Chr(10)
bodytext = bodytext & "Send email with queue." & Chr(13) & Chr(10) & Chr(13) & Chr(10)
bodytext = bodytext & "From:Tester" & Chr(13) & Chr(10)
'{$var_srecord:address} will be replaced by EASendMail automatically.
bodytext = bodytext & "To:{$var_srecord:email}" & Chr(13) & Chr(10) & Chr(13) & Chr(10)
bodytext = bodytext & "Your id in database is {$var_srecord:id}." & Chr(13) & Chr(10)

oSmtp.bodytext = bodytext

MsgBox "start to send email ..."

If oSmtp.SendMailToQueue() = 0 Then
MsgBox "email was sent to queue successfully!"
Else
MsgBox "failed to send email with the following error:" & oSmtp.GetLastErrDescription()
End If

you don't have to set easendmail service smtp server, because you have specified it in your code.

it is likely there is an error to access your database, please go to easendmail service manager->journal->system error and see if there is any error with database.

Hi Ivan

Had a look, seems you right I got this error message:

ErrorCode
-2147467259

Description
desc=[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified;source=Microsoft OLE DB Provider for ODBC Drivers;conn=False;sql=SELECT * FROM EmailTest

Any advice on what I need to change?

the correct connection string is:


Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Users\Public\TestDatabase.accdb;Uid=;Pwd=;

change your code like this

oSmtp.AddHeader "X-Data-Connection", _
"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Users\Public\TestDatabase.accdb;Uid=;Pwd=;"

By the way, if you still get this error, please

please download and install the following package, if your OS is x64, please install both 32 (please install 32bit at first) and 64 drivers
http://www.microsoft.com/en-us/download/details.aspx?id=13255 

That worked! thank you Ivan 🙂