Problem querying a database to send emails

G

Guest

I am trying to send one email with a attached report to people on a query
("Current_Case_Query_Within_15_Days"), the exact field in the query that
contains the email address is "UserID" but I continue to get an error on that
(UserID) field/part of the code. Can anyone help? I am new at this coding
part of access. See my code below.

Thanks in advance,
Raymond


Sub SendMessage()
Dim qdf As DAO.QueryDefs
Dim db As DAO.Database
Dim rsRecip As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim clsSendObject As accSendObject

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

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

' Open the recordset so you can loop through it.
Set rsRecip = db.OpenRecordset("Current_Case_Query_Within_15_Days")

Do Until rsRecip.EOF
' Add recipient
' (your code)
Set objOutlookRecip = .Recipients.Add(rsRecip.UserID("EMail"))
objOutlookRecip.Type = olTo
rsRecip.MoveNext
Loop


With objOutlookMsg
' Add the To recipient(s) to the message.


' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft"
Outlook ""
.Body = "This is the body of the message." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send

End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub
 
G

Guest

Hi Raymond,

Do you have those two very important words "Option Explicit" shown as the
second line of code in your module? Have you attempted to compile your code
(Debug > Compile ProjectName)? If not, add it to your module. See this Gem
Tip for instructions on how to configure your Visual Basic Editor (VBE) to
always include this at the top of all new modules:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

The reason I ask is that I get a compile error on this declaration:

Dim clsSendObject As accSendObject <---User defined type not defined.

After commenting the above line of code out, I find another compile error on
this line of code:

Set objOutlookRecip = .Recipients.Add(rsRecip.UserId("EMail"))

I suspect that you need this, instead:

Set objOutlookRecip = objOutlookMsg.Recipients.Add(rsRecip("UserId"))


If you haven't already seen it, you can use this KB article as a guide:

http://support.microsoft.com/?id=318881

As an alternative, you can try using code that I show on page 17 of my copy
of Access Links.doc. You are welcome to download a copy here:

http://home.comcast.net/~tutorme2/samples/accesslinks.zip

You can use the following function (not included in Access Links.doc) to
create a semicolon delimited list of e-mail addresses. This serves as
strRecipients for the SendMail function shown on page 17:

Function BulkEmail() As String
On Error GoTo ProcError

'Purpose: Return a string containing all the email addresses to mail to.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strOut As String
Dim lngLen As Long
Const conSEP = ";"

strSQL = "SELECT [ContactName] FROM [Customers] " _
& "WHERE [ContactName] Is Not Null;"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

With rs
Do While Not .EOF
strOut = strOut & ![ContactName] & conSEP
.MoveNext
Loop
End With

lngLen = Len(strOut) - Len(conSEP)

If lngLen > 0 Then
BulkEmail = Left$(strOut, lngLen)
End If

Debug.Print BulkEmail

ExitProc:
If Not rs Is Nothing Then
rs.Close: Set rs = Nothing
End If
Set db = Nothing
Exit Function

ProcError:
MsgBox Err.Number & ": " & Err.Description, _
vbCritical, "Error in BulkEmail function..."
Resume ExitProc
End Function


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
G

Guest

Tom, YOU KNOW YOUR STUFF! It worked like a charm. I just need to figure out
how to add an attachment (a report from the same db).

Thanks,

Raymond

Tom Wickerath said:
Hi Raymond,

Do you have those two very important words "Option Explicit" shown as the
second line of code in your module? Have you attempted to compile your code
(Debug > Compile ProjectName)? If not, add it to your module. See this Gem
Tip for instructions on how to configure your Visual Basic Editor (VBE) to
always include this at the top of all new modules:

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions

The reason I ask is that I get a compile error on this declaration:

Dim clsSendObject As accSendObject <---User defined type not defined.

After commenting the above line of code out, I find another compile error on
this line of code:

Set objOutlookRecip = .Recipients.Add(rsRecip.UserId("EMail"))

I suspect that you need this, instead:

Set objOutlookRecip = objOutlookMsg.Recipients.Add(rsRecip("UserId"))


If you haven't already seen it, you can use this KB article as a guide:

http://support.microsoft.com/?id=318881

As an alternative, you can try using code that I show on page 17 of my copy
of Access Links.doc. You are welcome to download a copy here:

http://home.comcast.net/~tutorme2/samples/accesslinks.zip

You can use the following function (not included in Access Links.doc) to
create a semicolon delimited list of e-mail addresses. This serves as
strRecipients for the SendMail function shown on page 17:

Function BulkEmail() As String
On Error GoTo ProcError

'Purpose: Return a string containing all the email addresses to mail to.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strOut As String
Dim lngLen As Long
Const conSEP = ";"

strSQL = "SELECT [ContactName] FROM [Customers] " _
& "WHERE [ContactName] Is Not Null;"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

With rs
Do While Not .EOF
strOut = strOut & ![ContactName] & conSEP
.MoveNext
Loop
End With

lngLen = Len(strOut) - Len(conSEP)

If lngLen > 0 Then
BulkEmail = Left$(strOut, lngLen)
End If

Debug.Print BulkEmail

ExitProc:
If Not rs Is Nothing Then
rs.Close: Set rs = Nothing
End If
Set db = Nothing
Exit Function

ProcError:
MsgBox Err.Number & ": " & Err.Description, _
vbCritical, "Error in BulkEmail function..."
Resume ExitProc
End Function


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________


Raymond said:
I am trying to send one email with a attached report to people on a query
("Current_Case_Query_Within_15_Days"), the exact field in the query that
contains the email address is "UserID" but I continue to get an error on that
(UserID) field/part of the code. Can anyone help? I am new at this coding
part of access. See my code below.

Thanks in advance,
Raymond


Sub SendMessage()
Dim qdf As DAO.QueryDefs
Dim db As DAO.Database
Dim rsRecip As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim clsSendObject As accSendObject

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

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

' Open the recordset so you can loop through it.
Set rsRecip = db.OpenRecordset("Current_Case_Query_Within_15_Days")

Do Until rsRecip.EOF
' Add recipient
' (your code)
Set objOutlookRecip = .Recipients.Add(rsRecip.UserID("EMail"))
objOutlookRecip.Type = olTo
rsRecip.MoveNext
Loop


With objOutlookMsg
' Add the To recipient(s) to the message.


' Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft"
Outlook ""
.Body = "This is the body of the message." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send

End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub
Click to expand...
Click to expand...
 
G

Guest

Hi Raymond,

You can use DoCmd.SendObject if you only need to send one report. If your
users have the free Microsoft Access Snapshot viewer utility installed, then
you can easily automate sending a report as a .snp file.

If you'd prefer to send the report as a .pdf file, then I believe you will
need to first save the report as a .pdf to an external file, and then use
your current method (since SendObject is only good for sending objects within
the Access database). The example I show on page 17 of my Access Links.doc
Word document shows how to send one or more files as attachments. There are
various ways of creating a .pdf, some with free utilities and others with
not-so-expensive utilities that interface nicely with Access. So, I guess the
first question that needs to be answered is what format do you want to send
your report in?


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
G

Guest

One report to all users in snap will be fine but I am not sure how what the
path is to my current db? Here is my code but it could be all wrong. What
would the send object syntax be? Thanks again for all your help!

If Not IsMissing("Current Case Query Within 15 Days Report") Then
Set objOutlookAttach = .Attachments.Add("Current Case Query Within
15 Days Report")
End If
 
G

Guest

Hi Raymond,

Okay, so the Snapshot file format is fine, right? If so, that makes your
task a lot easier (IMHO).
but I am not sure how what the path is to my current db?
Click to expand...

I'm not sure why you need the path to your current database for this issue,
but you can get it using either currentdb.name or currentproject.fullname.

The syntax that you indicated is for attaching an external file. The syntax
for sending a report in the MS Access Snapshot format does not require that
the report be first saved to an external file.

I need to leave for work right now, so we can pick this up later on tonight.
Or, if you don't want to wait for me, you can try a Google Advanced group
search. Here is a link for a quickie search for similar threads that I've
been involved in. I think you can find the answer in one of these past
threads. Let me know, okay?

http://groups.google.com/groups?as_...5&as_maxd=16&as_maxm=10&as_maxy=2007&safe=off


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
G

Guest

Tom, Yes snapshot is what I am looking for. I just need to attach a report
from my currect db to the following code that works
minus the attachment part. What would the code be to attach a report called
"Current Case Query Within 15 Days Report" to the code below?

Thanks again,

Raymond

Sub SendMessage()
Dim qdf As DAO.QueryDefs
Dim db As DAO.Database
Dim rsRecip As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim clsSendObject As accSendObject

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

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

' Open the recordset so you can loop through it.
Set rsRecip = db.OpenRecordset("Current Case Query Within 15 Days")

Do Until rsRecip.EOF
' Add recipients

Set objOutlookRecip = objOutlookMsg.Recipients.Add(rsRecip("UserId"))
objOutlookRecip.Type = olTo

Set objOutlookRecip = objOutlookMsg.Recipients.Add(rsRecip("Branch
Chief id"))
objOutlookRecip.Type = olCC

Set objOutlookRecip = objOutlookMsg.Recipients.Add(rsRecip("ard id"))
objOutlookRecip.Type = olCC
rsRecip.MoveNext

Loop

With objOutlookMsg


' Set the Subject, Body, and Importance of the message.
.Subject = "CASE ACTION REQUIRED"

.Body = "This is to inform you that you have a case(s) that may
require action on your part. Attached please find the Case Actions Required
Report." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance

DoCmd.SendObject acReport, "Current Case Query Within 15 Days
Report", OutputFormat = acFormatSNP


' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send

End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub
 
G

Guest

Hi Raymond,

You cannot stick DoCmd.SendObject in the middle of your existing procedure
like this. Well, I suppose you *can* do it, but you really don't want to:
.Importance = olImportanceHigh 'High importance

DoCmd.SendObject acReport, "Current Case Query Within 15 Days
Report", OutputFormat = acFormatSNP
Click to expand...

If you'd like to use the method that you have been working on so far, then
try the following modifications.

Notes:
1.) This method will require that you first export the report, as a .snp
file, to your hard drive. I used DoCmd.OutPutTo to accomplish this. I
included the use of named parameters in this example. I prefer named
parameters myself versus positional parameters separated only with commas.

2.) I removed two declarations from your code:
Dim qdf As DAO.QueryDefs <---Was not being used.
and
Dim clsSendObject As accSendObject <---??? I can't get this to
compile.

3.) I added a string variable, strAttachmentPath, and used this as indicated
in KB 318881, except that I'm not passing in the attachment path as a
required parameter.
http://support.microsoft.com/?id=318881

4.) I could not easily test the rsRecip("Branch Chief id") and rsRecip("ard
id") parts, but I'll trust that that works for you.

5.) I added an error handler, along with code in the ExitProc section to
cleanup (close recordset & set = Nothing, and set other variables = Nothing).


'*************Begin Code***********************

Sub SendMessage()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rsRecip As DAO.Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim strAttachmentPath As String

strAttachmentPath = CurrentProject.Path _
& "\Current Case Query Within 15 Days Report.snp"

' Save the report to disk as a Snapshot file
DoCmd.OutputTo ObjectType:=acOutputReport, _
ObjectName:="Current Case Query Within 15 Days Report", _
OutputFormat:=acFormatSNP, _
OutputFile:=strAttachmentPath, Autostart:=False

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

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

' Open the recordset so you can loop through it.
Set rsRecip = db.OpenRecordset("Current_Case_Query_Within_15_Days")

Do Until rsRecip.EOF

' Add recipients
Set objOutlookRecip = _
objOutlookMsg.Recipients.Add(rsRecip("UserId"))
objOutlookRecip.Type = olTo

Set objOutlookRecip = _
objOutlookMsg.Recipients.Add(rsRecip("Branch Chief id"))
objOutlookRecip.Type = olCC

Set objOutlookRecip = _
objOutlookMsg.Recipients.Add(rsRecip("ard id"))
objOutlookRecip.Type = olCC

rsRecip.MoveNext

Loop

With objOutlookMsg

' Set the Subject, Body, and Importance of the message.
.Subject = "CASE ACTION REQUIRED"

.Body = "This is to inform you that you have a case(s) that " _
& "may require action on your part. Attached please find " _
& "the Case Actions Required Report." & vbCrLf & vbCrLf

.Importance = olImportanceHigh 'High importance

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next

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

.Send

End With


ExitProc:
'Cleanup
If Not rsRecip Is Nothing Then
rsRecip.Close: Set rsRecip = Nothing
End If
Set db = Nothing
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in SendMessage Procedure..."
Resume ExitProc
Resume
End Sub

'*************End Code************************

That said, using the SendObject method would still be easier, IMHO, if you
only need to send one attachment, and that attachment represents an object in
your database, such as a report. It's getting pretty late for me now, so if
you are interested in pursuing this method, we can resume tomorrow evening.
However, for now, I thought I'd try to help you finish the path you've
started down.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
G

Guest

Thanks again. I really do not need to export the report to my harddrive if I
can just get the report from the db to attach to the email. I played around
last night with the following command and it would attach the report like I
want it to but then the recipients would not appear on the email then I moved
the statement else where in the code and the recipients would appear but not
the report. Where should I place this statement so that I get both to work?

Thanks again,
Reinaldo

DoCmd.SendObject acSendReport, "Current Case Query Within 15 Days Report",
acFormatSNP
 
G

Guest

Tom,

That did it thank you very much for all your help! You are a true solutions
provider!
 
G

Guest

Hi Raymond,
That did it thank you very much for all your help! You are a true solutions
provider!
Click to expand...

You're welcome. Glad to help!


From message posted approx. 6 1/2 hours earlier:
Thanks again. I really do not need to export the report to my harddrive if I
can just get the report from the db to attach to the email. I played around
last night with the following command and it would attach the report like I
want it to but then the recipients would not appear on the email then I moved
the statement else where in the code and the recipients would appear but not
the report. Where should I place this statement so that I get both to work?

Thanks again,
Reinaldo

DoCmd.SendObject acSendReport, "Current Case Query Within 15 Days Report",
acFormatSNP
Click to expand...

Did you want to pursue this easier method? I'm a bit confused, reading this
earlier reply versus the one you just made. The answer to using
DoCmd.SendObject is to pass a colon delimited string of e-mail addresses to
the To, CC, or BCC parameters, keeping in mind, of course, any restrictions
imposed by your ISP on the number of e-mail addresses. The colon delimited
string can be created running the BulkEmail function that I provided in my
first reply to this thread. In your case, you could make three calls to this
function, to get concatenated strings for each parameter. So, for example,
rsRecip("UserrID") could be used to create a string containing a semicolon
delimited list of e-mail addresses for the "To" parameter. Likewise,
rsRecip("Branch Chief id") would be used to build up a string containing a
semicolon delimited list of e-mail addresses for the "CC" parameter, and
rs("ard id") for the "BCC" parameter. Something like this:

'*********Begin Code for SendObject Method************

Sub SendMessage()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rsRecip As DAO.Recordset
Dim strTo As String
Dim strCC As String
Dim strBCC As String
Dim strBody As String

Set db = CurrentDb

strBody = "This is to inform you that you have a case(s) that " _
& "may require action on your part. Attached please find " _
& "the Case Actions Required Report." & vbCrLf & vbCrLf

' Open the recordset so you can loop through it.
Set rsRecip = db.OpenRecordset("Current_Case_Query_Within_15_Days")

Do Until rsRecip.EOF

' Add recipients
strTo = strTo & rsRecip("UserId") & ";"
strCC = strCC & rsRecip("Branch Chief id") & ";"
strBCC = strBCC & rsRecip("ard id") & ";"

rsRecip.MoveNext
Loop

DoCmd.SendObject _
ObjectType:=acReport, _
ObjectName:="Current Case Query Within 15 Days Report", _
OutputFormat:=acFormatSNP, _
To:=strTo, CC:=strCC, BCC:=strBCC, _
Subject:="CASE ACTION REQUIRED", _
MessageText:=strBody, _
EditMessage:=True

ExitProc:
'Cleanup
If Not rsRecip Is Nothing Then
rsRecip.Close: Set rsRecip = Nothing
End If
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in SendMessage Procedure..."
Resume ExitProc
Resume
End Sub

'*************End Code************************


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sending Outlook emails from Access 2003 to recipients in a query 3
how send an access report besides a C:\attachment already included 5
Adding new lines in plain text to outlook message 1
Formatting of outlook email body that is created with Access. 1
Uknown error on send attachment. 2
Access 2000 Outlook 2007 1
Add Hyperlink in Email 4
Outlook email with code - adding attachments 4

Top