Emailing via vb

[Rengade]

Please help I have been Emailing data from Access via merge to mail option in
word, but wish to do it via acc directly.

I can use the below code to successfully send 1x email from a form:

stTo = EmailName
stSubject = "***Epex training confirmation***"
stFirstName = number
stText = "Dear " & stFirstName & Chr$(13) & Chr$(13)

DoCmd.SendObject , , acFormatTXT, stTo, , , stSubject, stText, -1

But I have a table like below:

EmailName (john/dave/fred etc.)
Date fo birth (01-01-70/ 02-02-89/ 01-09-52)

How can I get access to create 3x emails for the above information with only
1 button click??

Many many thanks

Renegade

 

[Douglas J. Steele]

See whether you can find what you're looking for in the Access Email FAQ
that Tony Toews has at http://www.granite.ab.ca/access/email.htm

 

[a a r o n . k e m p f]

I'd reccomend doing this from the DB Server-- using XP_SendMail.

I just think that it is a security hole-- by definition-- to setup
spam senders on 20 different desktops.

Put it on SQL Server, send email from the database server.. and secure
it well.
It's called a stored procedure-- and if your database doesn't support
them; it is time to upsize, upsize, upsize.

It's basically the same as a QUERY in MS Access

But it's not limited to a single select or crud operation.

-Aaron

 

[Rengade]

I am now trying the following module, but get error message (user defined
type not defined) with MyDB As Database highlighted........ any ideas please

Option Compare Database
Option Explicit

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

 

[Douglas J. Steele]

You don't have a reference set to DAO.

Assume you're using Access 2003 or older, go into the VB Editor and select
Tools | References from the menu bar. Scroll through the list of available
references and select Microsoft DAO 3.6 Object Library.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am now trying the following module, but get error message (user defined
type not defined) with MyDB As Database highlighted........ any ideas
please

Option Compare Database
Option Explicit

Sub SendMessages(Optional AttachmentPath)

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("tblMailingList")
MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![EmailAddress]

With objOutlookMsg
' Add the To recipients to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the Cc recipients to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, the Body, and the Importance of the e-mail
message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add the attachment to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve the name of each Recipient.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

I'd reccomend doing this from the DB Server-- using XP_SendMail.

I just think that it is a security hole-- by definition-- to setup
spam senders on 20 different desktops.

Put it on SQL Server, send email from the database server.. and secure
it well.
It's called a stored procedure-- and if your database doesn't support
them; it is time to upsize, upsize, upsize.

It's basically the same as a QUERY in MS Access

But it's not limited to a single select or crud operation.

-Aaron