Solved Create an Access mail merge to email from Outlook (1 Viewer)

Number11

Member
Local time
Today, 20:11
Joined
Jan 29, 2020
Messages
607
Hoi,

I need to have access send on a daily basis emails to customers which the data is stored in a table.

Any suggestions for the best way, can i just set up access to send the emails? and also attached two pdf files
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:11
Joined
Feb 28, 2001
Messages
27,001
In the "Similar Threads" list below this thread, there is something titled "Add attachment to email from search result link" that includes some very simple-minded but quite direct code to send e-mail from Access with attachments. Might I suggest you look at that?
 

Number11

Member
Local time
Today, 20:11
Joined
Jan 29, 2020
Messages
607
so this is what i have and it works in that it just sends an email to the first contact in the table, how do i set this to run through all..


Code:
Dim MyDB As Database

Dim MyRS As Recordset

Dim OutlookApp As Outlook.Application

Dim OutlookMail As Outlook.MailItem

Set MyDB = CurrentDb

Set MyRS = MyDB.OpenRecordset("test")

MyRS.MoveFirst

Set OutlookApp = New Outlook.Application

Set OutlookMail = OutlookApp.CreateItem(olMailItem)

With OutlookMail

.BodyFormat = olFormatHTML

.HTMLBody = "<p style='font-family:Tahoma;'>Account Number: " & MyRS![Customer_Number] & _

"<p style='font-family:Tahoma;'>Hi " & MyRS![Forename] & " <sp> " & MyRS![Surname] & _

"<p style='font-family:Tahoma;'>Test Here</p>" & _

"<p style='font-family:Tahoma;'>Test Here</p>" & _

"<p style='font-family:Tahoma;'>Test Here</p>" & _

"<p style='font-family:Tahoma;'>Test Here</p>" & _

"<p style='font-family:Tahoma;'>Test Here</p>" & _

"<h2 style='text-align:Left;'><strong></strong></h2>"

.To = ""

.BCC = MyRS![Email]

.Subject = "Test"

'.Attachments.Add "c:\zip\file1..PDF"

'.Attachments.Add "c:\zip\file21..PDF"

.Send

‘.Display
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:11
Joined
Oct 29, 2018
Messages
21,358
You'll need a loop. You're using MoveFirst, but you don't have MoveNext.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:11
Joined
Feb 28, 2001
Messages
27,001
Here is a link to a commercially-oriented educational site that talks about loops and how to set them up.


In overview for your case, you pick two points in your code. Point 1 is where you actually start to build the message. This will be AFTER the point where you create the Outlook app object but before you create the mail object. Point 2 is where you have just sent the completed message and are done with it, ready to start on the next message.

Before point 1 you set up your chosen loop starter syntax. After point 2 you set up your chosen loop repetition syntax. Since you are using data from a recordset, you might want to look at a loop that uses Do While Not recordset.EOF which will repeat the loop as long as you have data. At point 2, two things happen - first, the recordset.MoveNext and second, the Loop statement that repeats the code enclosed by points 1 and 2. The .MoveNext and Loop must occur in the order as stated.

Always remember this: When you create an external app object (in this case, Outlook), you are also creating an internal link, in essence a control channel, between Access and the other object, but that other object is in a separate task due to the way Windows does things. That means that before you are done with this, you must tell your object to close itself down. Otherwise you will leave a "dangling" task for which the control connections are severed when Access exits. At this point the other process is uncontrolled. Oh, it won't DO anything, but it takes up memory and can lead to resource issues. The only way to clear dangling tasks is with Task Manager to find them, then do an End Process manually.

If, on the other hand, you reach the point in your code where the loop exits (because you hit the .EOF condition), that is Point 3 - where you do an object.Quit command before exiting that code segment. Though it usually doesn't matter THAT much, that is also where you would do a recordset.Close as a precaution. Some versions of Windows and of Access have rarely shown issues of dangling recordsets. It's a rare condition that depends on which version of SMB protocol you are running and whether "buffer leasing" is going on at the time. It's easier to just close the recordset and sidestep the question altogether.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:11
Joined
Oct 29, 2018
Messages
21,358
Thanks
so how would i add that please just add ...



MyRS.MoveFirst
Do Until MyRS.EOF
MyRS.MoveNext
Loop
Yes, but insert your Outlook code between Do Until and MoveNext
 

Number11

Member
Local time
Today, 20:11
Joined
Jan 29, 2020
Messages
607
Yes, but insert your Outlook code between Do Until and MoveNext
Dim MyDB As Database
Dim MyRS As Recordset
Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("test")
MyRS.MoveFirst
Do Until MyRS.EOF

Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(olMailItem)
With OutlookMail
.BodyFormat = olFormatHTML
.HTMLBody = "<p style='font-family:Tahoma;'>Account Number: " & MyRS![Customer_Number] & _
"<p style='font-family:Tahoma;'>Hi " & MyRS![Forename] & " <sp> " & MyRS![Surname] & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<h2 style='text-align:Left;'><strong></strong></h2>"

.To = ""
.BCC = MyRS!
.Subject = "Test"
.Attachments.Add "c:\zip\file1..PDF"
.Attachments.Add "c:\zip\file21..PDF"
.Send
‘.Display

MyRS.MoveNext
Loop

throws up error on "Loop"

1648653242384.png
 
Last edited:

Number11

Member
Local time
Today, 20:11
Joined
Jan 29, 2020
Messages
607
Can you post the entire code? Or, are you able to post a sample db?
Code:
Dim MyDB As Database
Dim MyRS As Recordset
Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("test")
MyRS.MoveFirst
Do Until MyRS.EOF

Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(olMailItem)
With OutlookMail
.BodyFormat = olFormatHTML
.HTMLBody = "<p style='font-family:Tahoma;'>Account Number: " & MyRS![Customer_Number] & _
"<p style='font-family:Tahoma;'>Hi " & MyRS![Forename] & " <sp> " & MyRS![Surname] & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<h2 style='text-align:Left;'><strong></strong></h2>"

.To = ""
.BCC = MyRS!
.Subject = "Test"
.Attachments.Add "c:\zip\file1.PDF"
.Attachments.Add "c:\zip\file21.PDF"
.Send
‘.Display

MyRS.MoveNext
Loop

end With
End Sub
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 13:11
Joined
Oct 29, 2018
Messages
21,358
Is that code behind a button? How many emails are we trying to send out?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:11
Joined
Jul 9, 2003
Messages
16,245
Change
Code:
Loop
To:-
Code:
'Loop
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:11
Joined
Jul 9, 2003
Messages
16,245
I suspect these:-
Code:
Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(olMailItem)

should be before the "Do Until" like this:-
Code:
Dim MyDB As Database
Dim MyRS As Recordset
Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("test")
Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(olMailItem)
MyRS.MoveFirst
Do Until MyRS.EOF
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:11
Joined
Sep 21, 2011
Messages
14,047
No, I would say the New outlook App is outside the loop and CreateItem inside?

This is what I have used in the past.

Code:
    Set objOutlook = CreateObject("Outlook.Application")

.......
    Do While Not rs.EOF
        ' Set flag and field to check
        blnSameClientType = True
        strClientType = rs!Client & rs!TranType
        strType = rs!TranType
        
        ' Create the message if first time we are in a different client or tran type.

        Set objOutlookMsg = objOutlook.CreateItemFromTemplate(strTemplatePath & "\SSAFA Email.oft")
        With objOutlookMsg
 

Number11

Member
Local time
Today, 20:11
Joined
Jan 29, 2020
Messages
607
I suspect these:-
Code:
Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(olMailItem)

should be before the "Do Until" like this:-
Code:
Dim MyDB As Database
Dim MyRS As Recordset
Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem
Set MyDB = CurrentDb
Set MyRS = MyDB.OpenRecordset("test")
Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(olMailItem)
MyRS.MoveFirst
Do Until MyRS.EOF
changed as above still same issue on the loop
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:11
Joined
Sep 21, 2011
Messages
14,047
I would be putting the End With after the .display and indenting the code?
 

Number11

Member
Local time
Today, 20:11
Joined
Jan 29, 2020
Messages
607
Moved loop to after
End With and now getting's this error..


1648737562031.png

on debug this is the line highlighted in yellow

.BodyFormat = olFormatHTML




end With
MyRS.MoveNext
Loop
End Sub
 

Number11

Member
Local time
Today, 20:11
Joined
Jan 29, 2020
Messages
607
Resolved it back changing the method - Thanks


Code:
  Dim MyDB As Database
  Dim MyRS As Recordset
  Dim MyForm As Form
  Dim objOutlook As Outlook.Application
  Dim objOutlookMsg As Outlook.MailItem
  Dim objOutlookRecip As Outlook.Recipient
  Dim objOutlookAttach As Outlook.Attachment
  Dim TheAddress As String
  Dim TheBody As String
 
  
  Set MyDB = CurrentDb
  Set MyRS = MyDB.OpenRecordset("test")
    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![Email]
 
  TheBody ="<p style='font-family:Tahoma;'>Account Number: " & MyRS![Customer_Number] & _
"<p style='font-family:Tahoma;'>Hi " & MyRS![Forename] & " <sp> " & MyRS![Surname] & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<p style='font-family:Tahoma;'>Test Here</p>" & _
"<h2 style='text-align:Left;'><strong></strong></h2>"


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

        
        ' Set the Subject, the Body, and the Importance of the e-mail message.
        .To = MyRS![Email]
        .Subject = "Test"
        .HTMLBody = TheBody     
            
        
    
        ' 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
 

Users who are viewing this thread

Top Bottom