Send Multiple Files from Access to Outlook for Emails

[dipitiduda2]

Hello All,

Need assistance for MS Access to Outlook XP tasks using Visual Basic....here's the problem:

Have a dBase set up that generates reports based on multiple ID's with page breaks separating the distinct records. This report is based on a CLICK event in the form's sub procedure. The form's sub CALLS the basModule that then writes the data as a .pdf file (using a standard module) and sends it to Outlook as email attachments (or so I think-cannot test it far enough to tell).

The problem arises when Outlook detects the vb script trying to access Outlook and a MsgBox (vbYesNoHelp) states: "A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected, it may.......". It does not allow me to send unsecured messages for a limited time, only allows me to CLICK the 'Yes' option twice. And, then I CLICK on a MsgBox (vbOKOnly) that states: "Object doesn't support this property or method.".

Question: What property or method is MS Access referring to? I cannot trace the code back to the debugger that is generating this error message. Also, why will MS Outlook not allow me to override the unsecured message function?

I have provided coding below for the sub procedure where the error message is occurring.

Any assistance is sincerely appreciated.

Private Sub cmdEMailRpts_Click()
On Error GoTo Err_cmdEMailRpts_Click

Dim stDocName As String
Dim Name As String
Dim objOutLook As Outlook.Application
Dim objOutLookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

'Set the Oulook session.
Set objOutLook = CreateObject("Outlook.Application&quot
'Create the message.
Set objOutLookMsg = objOutLook.CreateItem(olMailItem)

Call SaveReportAsPDF("rpt_ExpenseRpt", "C:\work\" & Name & ".pdf&quot

With objOutLookMsg
'Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("mball@halecounty.org&quot
objOutLook.Recip.Type = olTo
'Set the Subject, Body, and Importance of the message.
.Subject = "This is an Automation test with Microsoft Outlook"
.Body = "Last test - I promise." & vbCrLf & vbCrLf
.Importance = olImportanceHigh 'High importance
'Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If
'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

Exit_cmdEMailRpts_Click:
Exit Sub

Err_cmdEMailRpts_Click:
MsgBox Err.Description
Resume Exit_cmdEMailRpts_Click

End Sub

 

[TLady]

Wild guess
Do you have PDFWriter installed?
...just asking
TIA

 

[dipitiduda2]

Yep....got it installed. One thing that occurs to me now is, do I have to have the Contacts set up in Outlook BEFORE I try to send the emails over from Access? I thought not, because the unique IDs will change based on the reports that are generated in Access each time the sub procedure is run.

Any other ideas? Thanks!

 

[TLady]

See, I have Table with E-Mails, so I am OpeningRecordset and sending Report to each E-MailAddress in "E-Mail" field.
Table gets refreshed every run to repopulate E-Mail field in case there are changes.
But if you are using Contact in Outlook - they must be there before run.
TIA

 

[dipitiduda2]

I don't have to use the Contacts in Outlook.....I just don't know any other method for sending the emails. My main purpose is to get the data from Access to Outlook and then send the reports to each individual (or grouped) email address. The relationship for the records is:

Vendor ID = Email ID; has 1 to 1 relationship

There could be more than one report that has the same Vendor ID/Email ID listed on it.

As I see it, I have two choices for emailing:
1. Send each report page to one email address based on the Vendor ID and Email ID match. Potentially, one email addressee (person) could receive up to 15 single emails containing one attachment in each email.

2. Or, if I could find a way to group the Vendor ID with the Email ID and "push" those reports that match that specific criteria into a single email submission, that would be optimal.

Note: I created my report (rpt_ExpenseRpts) from a query NOT a table.....due to the extra criteria that I need to generate the report.

Thanks!

 

[TLady]

'Open E-Mail list record set (SO< GET THOSE EMAILS INTO TABLE or QUERY qry_Manager_EMails as I did)
Set parsRS = CurrentDb().OpenRecordset(&quot;qry_Manager_EMails&quot;, dbOpenDynaset)
parsRS.MoveLast
parsRS.MoveFirst

'Format & E-Mail each sheet (I was sending each sheet of the same WKBook to different recipient)
For i = 1 To oBook.Sheets.Count
If oBook.Sheets(i).Visible = True Then
oBook.Sheets(i).Select
-------------------------------------------------
'Parse out to E-Mail
DistrictName = oBook.Sheets(i).Name
parsRS.FindFirst (&quot;[Level]='&quot; & DistrictName & &quot;'&quot
If parsRS.NoMatch Then
MsgBox &quot;No E-Mail address for district &quot; & DistrictName
-----------------------------------------------------
Else
oBook.Sheets(DistrictName).Copy 'This is where Parsing starts
oExcel.ActiveWorkbook.Close
EMailReport DMDeliverPath, parsRS![District_ManagerFirstName], parsRS!
End If
End If
End If ' End of formating sheet
Next i 'Format Next Sheet

You'll have to trim it a little to your needs
TIA

 

[CosmoKramer]

At first glance....

Is this code necessary?:

 For Each objOutlookRecip In .Recipients
            objOutlookRecip.Resolve
            If Not objOutlookRecip.Resolve Then
                objOutLookMsg.Display
            End If
        Next

I use similar code for e-mailing, but I don't use code like this.....

 

[dipitiduda2]

TLady.....thanks for the help....will work on integrating this code into my procedure.

CosmoKramer.....re: code necessary.....Not sure if this code is necessary or not....copied it from another newsgroup post. Would you consider sending me the code that you DO use?

Again, thanks!

 

[CosmoKramer]

Sure, here is an example I use to send a spreadsheet previously generated by another Access function to a few users:

Function Sendemail_WeekStat()

'******begin code******
Dim email, ref, origin, destination, notes, strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strFileName As String

'***creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
strEmail = "cosmokramer@yahoo.com;jerryseinfeld@aol.com"
strBody = "The attached file contains the department volume counts for week ending " & Format(Date - 4, "mm/dd") & "." & _
          vbCrLf & vbCrLf & _
          "The numbers within are based on the Premier UOS and criteria. This file reflects entries and adjustments entered through Tuesday evening (" & Format(Date - 1, "mm/dd") & ") " & _
          "at 8:00 P.M., on activity for the prior week." & _
          vbCrLf & vbCrLf & vbCrLf & _
          "Cosmo Kramer" & vbCrLf & _
          "Database Administrator" 

strFileName = "c:\WeeklyStatsWE" & Format(Date - 4, "mm-dd") & ".xls"
'
'***begin attachment code***

'***creates and sends email
With objEmail
    .To = strEmail
    .Subject = "Weekly Volume File - W/E " & Format(Date - 4, "mm/dd")
    .Body = strBody
    .Attachments.Add (strFileName) 'add attachment
    .Send
End With

'****end attachment code****


End Function

Let me know if this helps....

 

[dipitiduda2]

With both of your assistance.....sure to make this work.....will keep ya posted. Thanks!

 

[bblekfeld]

Hi, any luck with this? I have a report that is several multiple pages with a different email on each page. I would like an automatic way to divide up the pages and send them to their respective email addresses. Any suggestions on where to begin? They should be in PDF form, but I will live with snapshot form if that is easiest.

 

[dipitiduda2]

bblekfeld,

Yes, I did finally get this code working and it will allow you to generate your reports in MS Access XP, then attach PDF files (one for each page) and send through MS Outlook XP. I will post for you all next week. Sorry, I cannot stop to post right now.