Access VBA to Send Emails

[backfromthebush]

Hi all,

I need to write a piece of code that sends an email based on fields in a query.

The query name is "qryCurrentBalancesOwing". It has four fields that are required in the email - Name, EmailMain, EmailAlternate, BalancePos.

I need the code to cycle through the query table and send an email to the persons "EmailMain" and, if they have one, a CC to their "EmailAlternate."

The basic email needs to read something like this:-

Subject line: Name & " - reminder of balance owing!"

Body of email:

Hi [Name],
**This is an automatic generated email!**
This is to advise that final fees are now due.
Your current balance as of today, [todays date], is $[balance pos]

The remainder of the email is just a whole heap of text that doesnt change from person to person.

The actual font and look of the email isn't that important

**IF** it is possible and not too much of challenge, it would be awesome to have an option at the start of the code that brings up a message box vbYesNo to ask the user if they wish to send a "test" email first (this will send the first record in the query to the users own email). Even better would be if the user answers vbYes then an inputbox appears requesting the "test email" address.

Can anyone help me on this?

 

[Trevor G]

Take a look at this sample code. I have used this in the past for a client who wanted to send out invitations to some event.

You have to set the Reference in the VBA screen to use Outlook, so use Alt + F11 then the Tools Menu > Scroll down the list until you find Microsoft Outlook.

The code runs from a form and behind a command button.

Private Sub CmdPressemail_Click()
Dim olapp As Outlook.Application
Dim olmailmessage As Outlook.MailItem
Dim olrecipient As Outlook.Recipient

Dim StrMsg As String

Dim rcdSQL As Variant

Dim db As DAO.Database
Dim rcd As Recordset

Set db = CurrentDb()

Set rcdSQL = db.OpenRecordset("SELECT * FROM qryEmailAwaitingResponseEastMidlands WHERE [AwardEventAttendance]='East Midlands'")

Set olapp = New Outlook.Application


Do Until rcdSQL.EOF

If rcdSQL!.Value <> "" Then
Set olmailmessage = olapp.CreateItem(olMailItem)

olmailmessage.Recipients.Add (rcdSQL![Email].Value)

olmailmessage.Subject = Me.txtsubject.Value
olmailmessage.Body = Me.txtbody.Value & vbCrLf




'If blnKnownRecipient = True Then
olmailmessage.Send
Set olmailmessage = Nothing
End If
rcdSQL.MoveNext


Loop
'olapp.Quit
Set olapp = Nothing
End Sub[/QUOTE]