Sending an Access Report via Outlook e-mail

[FSHOTT]

I am trying to create an Access application which selects a group of
Suppliers from a Suppliers e-mail info query then generates a Supplier Report
for each Supplier in the group and e-mails the each of them their respective
report for the month and year selected in the form. I have studied several
articles on this and have modified some of the example code but have run into
the following problem. In setting up the Outlook e-mail session I get the
error message "user-defined type not defined" when the code gets to the
statement Dim appOutlook As Outlook.Application. I went to the reference
libraries and checked the Microsoft Outlook 11.0 Object Library hoping this
would resolve the error message but had no such luck. Can someone point me in
a direction which will help me solve this? Thank You

 

[JP]

Can you post more of your code?

--JP

 

[FSHOTT]

Here is the code. Thanks for looking into this.




Private Sub Command92_Click()


On Error GoTo Err_Command92_Click
'Insert code to use selected supplier group filter supplier Email info, then
create and
'Email a Supplier Report Card to each Supplier in the selected group

'Record Set - setup
'For Access, define some object variables and make connections.
Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset

'Outlook - setup
'Define some object variables for Outlook

Dim appOutlook As Outlook.Application 'Refers to Outlook's Application
object.
Dim appOutlookMsg As Outlook.MailItem 'Refers to an Outlook e-mail
message.
Dim appOutlookRecip As Outlook.Recipient 'Refers to an Outlook e-mail
recipient.

'General variables.
Dim mySQL As String, eMailAddress As String, whereClause As String
Dim countEm As Integer, myMsg As String, mySubject As String, myIntro As
String
myRecordSet.ActiveConnection = myConnection
countEm = 0 'This will be used to keep track of the number of
messages sent.


'Start building the SQL statement for the recordset.
mySQL = "SELECT [qryGroupSelect].* FROM [qryGroupSelect] "

'Select Supplier Group Name to send Report cards to Form dropdown/list as
cboGroupName - Note do a requery after update?


'Include the cboGroupName selected in whereClause to filter the Supplier
E-Mail Info to only those in the group of suppliers selected
whereClause = " WHERE ([qryGroupSelect].[GroupName]=[cboGroupName]) AND
([qryGroupSelect].[SupplierEmailAddress] Is Not Null) "









'Finish the SQL statement.
mySQL = mySQL & whereClause



'Now let's open up the recordset and start going through,
record-by-record.
myRecordSet.Open mySQL, , adOpenStatic, adLockOptimistic
'Bail out if recordset gets no records.
If myRecordSet.RecordCount < 1 Then
MsgBox ("There are no records that meet the criterion. No messages
sent.")
Exit Sub
End If
myRecordSet.MoveFirst

'Create an Outlook session in the background.
Set appOutlook = CreateObject("Outlook.Application")

Do Until myRecordSet.EOF 'For each record in myRecordset...

'Get the e-mail address from current record of myRecordset.

eMailAddress = myRecordSet.Fields("SupplierEmailAddress")








'I do not understand what this code is supposed to do?
'If there's a # character in the eMail address...
If InStr(1, eMailAddress, "#") > 0 Then
'...then chop off the # and everything that follows it.
eMailAddress = Left(eMailAddress, InStr(1, eMailAddress, "#") - 1)
End If

'Need to revamp this code to pick up the e-mail subject and message from the
SupplierEmailInfo record set
'Create a new, empty e-mail message.
Set appOutlookMsg = appOutlook.CreateItem(olMailItem)

With appOutlookMsg 'Using the new, empty message...
' Address the new message.
Set appOutlookRecip = .Recipients.Add(eMailAddress)
appOutlookRecip.Type = olTo 'Sets message to normal outgoing
e-mail message.
' Fill in the Subject line and main body of message.
mySubject = "SWS Supplier Report Card for " &
myRecordSet.Fields("SupplierName") & " Me![cboMonth]" & " - " & "
Me![cboYear] "
myIntro = "Dear " & myRecordSet.Fields("EMailPersonName") & " " &
myRecordSet.Fields("EMailIntro")
myMsg = myIntro & " // " & myRecordSet.Fields("Message")


.Subject = mySubject 'Fill in the subject line.
.Body = myMsg 'Fill in the message body.

'Add attachments, if any, to the e-mail message.
'Add as attachment the Supplier Report card for the RecordSet
selected Supplier. Check the Report card to determine if BLANK. If so branch
to Record Count Increment
'Add code to create the Supplier Report Card attachment


'Add code to create check if BLANK and branch to Record Count
Increment if so.



'Add code to convert ACCESS report to Snapshot and/or PDF






.Attachments.Add (Me![something like myAttach]) 'Note
define myAttach above

'Send the completed E-mail
.Send 'Send the completed message.
End With



'Go to next Record after sending the current Record and Associated
Supplier Report Card
myRecordSet.MoveNext 'Next record in recordset
countEm = countEm + 1 'Keeps track of number of messages printed for
later display.
Loop 'Repeat with next record, if not eof.

myRecordSet.Close 'All records processed when loop done. Close
recordset.



DoCmd.SetWarnings False 'Temporarily hide warning messages.
'Update NewCustEmailSent to True.
mySQL = "UPDATE [Address Book] SET [Address Book].NewCustEmailSent =
True " & _
"WHERE [Address Book].NewCustEmailSent=False"
DoCmd.RunSQL mySQL
DoCmd.SetWarnings True 'Unhide warning messages.
End If
'Display feedback message.
myMsg = countEm & " message(s) sent to Outlook's Outbox"
MsgBox (myMsg)
'All done when loop done. Clean up and say bye-bye.
Set myRecordSet = Nothing
Set appOutlookMsg = Nothing
Set appOutlook = Nothing
Set myConnection = Nothing
End Sub


Exit_Command92_Click:
Exit Sub

Err_Command92_Click:
MsgBox Err.Description
Resume Exit_Command92_Click

End Sub