Sending A Query Via VBA

[Moonshine]

Hi, well after many hours of trying with the standard:

"DoCmd.SendObject acSendQuery, "Appendix A Data Required", acFormatXLS, .Fields![SW Email], , , "Data Needed For Appendix A Forms", rstSubject.Fields![Subject],0"

For some reason the above wouldnt work, it basically is in teh middle of a Do statement, it cycles a recordset, gets the Staff Members Name and Email address, it was the supposed to send an email, with a Excel Spreadsheet attached containing data relevant to them. When i ran this, it sent the first email but would not send the others. At the time there were 52 unique staff members in the record source, but would not send the other 51! It ran the updates to place the relevant information per staff member into a query, but just wouldnt email it.

So, i then look on the Knowledge base, and came up with another way to email, below.

This works fine, it sends out the 52 emails spot on, BUT i cant get it to send the results of a query, as you can see below its looking for an attachment. Does anyone know how i could change this to send the object query with the email? I dont want to have to export the spreadsheets one by one, email it, delete it and do all that for each staff member. At times this table could get to over 200 staff members.


Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

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

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

With objOutlookMsg
' Add the To recipient(s) to the message.
Set objOutlookRecip = .Recipients.Add("Scott Holmes")
objOutlookRecip.Type = olTo

' Set the Subject, Body, and Importance of the message.
.Subject = "Data Needed For Appendix A Forms"
.Body = rstSubject.Fields![Subject]
'.Importance = olImportanceHigh 'High importance

' Add attachments to the message.
Set objOutlookAttach = .Attachments.Add("Appendix A Data Required")

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

'.Save
.Send
End With

 

[Travis]

For your first solution you need to move the SendObjects command to its own Procedure (outside the loop).


For your second solution you would need to send the Query to a physical file to add as an attachment.

 

[Moonshine]

Hi, and thanks for the reply.

For the first part, it has to be in the DO statement, to send an email to each different staff member. If it was outside of this statement, it would send one email only to the first record it finds.

Secondly, as i said in the original post. I dont want to have to do this, at some point the amount of staff members it could find will be over 200, and the records per staff member could easily top 10000 to 15000. That would make for a large Excel file, so exporting a 15000 line excel spreadsheet, 200 times onto a network drive which 3000 other people use during the day, would take forever.

 

[Travis]

Actually for one the answer is "No it does not need to be inside the loop"

Example

Sub TestLoopEmail()

Do ...

   'Looping through RecordSet  ...
   
    If SendThisEmail(.Fields![SW Email],rstSubject.Fields![Subject]) = True Then
    'Email Successfully Sent
    Else
    'Email Failed
    End If


   'Move to the next record ...

Loop While...

End Sub

Function SendThisEmail(EmailAddress as String,Subject as string) as Boolean
On Error Goto ErrorHandler
DoCmd.SendObject acSendQuery, "Appendix A Data Required", acFormatXLS, EmailAddress, , , "Data Needed For Appendix A Forms", Subject,0
SendThisEmail=True
Exit Function
ErrorHandler:
SendThisEmail=False

End Function

You could set up other parameters (i.e.; Format, body text, object type, etc)

By doing this the SendObject is in a different Procedure and it seems to work better (does not hang).

 

[Moonshine]

Thanks again for the reply....

Im not getting something tho, if the SendObject is outside of the Loop, how can it send a seperate email to each staff member?

 

[Travis]

The SendObjects Function itself is in a diffenet Procedure then the Loop. You call this procedure from inside the loop. It has been found that the SendObjects Function does not perform well when it is in the same procedure as the Do...Loop.

 

[Moonshine]

Ahh, right cool!

Got it thanks

 

[Moonshine]

Well after trying, i still couldnt get it to do excatly what i wanted. So after lengthy calls to our IT department, i got them to allow me access to my computers C drive (yes, they really do block users from accessing their C drive) and from then i could create a temporary directory and extract all the spreadsheets into that, and then delete them afterwards.

To send 52 emails it takes about 3 minutes, so that's not to bad!

Thanks for all your help