Access automate Outlook

[JohS]

I would like to ask for advice where to look for sample code which can do
this:

-from Access via VBA code, loop through the e-mail field and send email to
each who has e-mail saved in the field.(I actually also would like the email
to attach a word document, but it is not important right now ). JohS

 

[Guest]

Are you familiar with the process for creating a recordset in code? Depending
on your needs, you can either send the same message to many receipients at
the same time (check with your ISP for specific limits to avoid the message
being flagged as spam) or you can send each receipient their own message.

If the Word document is the same document for all receipients, and you only
have the need for a single file attachment, then I would use code based on
DoCmd.SendObject to send the message to myself on the "To:" line, and use the
blind copy capability to send to however many people your ISP will accept (I
believe the limit is 50 for my ISP, which is Comcast). Using the BCC
capability is generally considered polite, so that you do not expose each
person's e-mail address to everyone else. If your recordset includes more
people than your ISP's specific limit, then you will have to use a loop with
a counter to process X number of records per message.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[SJ]

this isn't a loop but you can add that.

if you include the #If 6 = 7 Then -> #End If and comment out the
other variables and add outlook as a reference you will see the outlook
object model.

cheers


Private Sub SendMail_Click()
#If 6 = 7 Then
'declarations like this require a reference to the Outlook Type Library
'this can be a nuisance for installation and compilation
'therefore just declare the variables as objects and trap the run time
errors
'if Outlook is not installed
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
#End If

Dim objOutlook As Object
Dim objOutlookMsg As Object
Dim objOutlookRecip As Object
Dim objOutlookAttach As Object
If IsNull(Me.EmailAddress) Then
If MsgBox("There is no Email Recipient specified in the [Email To:]
box." & vbCrLf _
& "Do you wish to create the Email anyway?", vbQuestion _
+ vbYesNo, pubWorkbench) = vbNo Then Exit Sub
End If

On Error Resume Next
'Create the Outlook session.
Set objOutlook = GetObject(, "Outlook.Application") 'get reference
if Outlook is already running.
If Err.Number = 429 Then 'Error 429
occurs if Outlook is NOT running.
Err.Number = 0
Set objOutlook = CreateObject("Outlook.Application") 'Create a new
instance of the Outlook application.
If Err.Number = 429 Then 'Error 429 will
occur if MS Outlook 8.0 is not installed.
MsgBox "MS Outlook 8.0 is not installed on your computer"
Exit Sub
End If
End If

On Error GoTo Err_SendMail_Click

' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(0) 'olMailItem

With objOutlookMsg
' Add the To recipient(s) to the message.
If Not IsNull(Me.EmailAddress) Then
Set objOutlookRecip = .Recipients.Add(Me.EmailAddress)
objOutlookRecip.Type = 1 'olTo
End If

' Set the Subject and Body of the message.
.Subject = Me.Jobs_Description
.Body = "put the msg here"
If Not IsNull(Me.DirectoryName) Then
Set objOutlookAttach = .Attachments.Add(Me.DirectoryName & "\" &
Me.FileName)
Else
'there is no attachment
' Set the Subject and Body of the message.
.Subject = Me.DocumentsOut_Description
.Body = vbCrLf & Details
End If

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next

' display message
.Display

End With
Set objOutlook = Nothing

Exit_SendMail_Click:
Exit Sub

Err_SendMail_Click:
MsgBox Err.Description, vbInformation, pubWorkbench
Resume Exit_SendMail_Click
Resume
End Sub