Completely Automate Email Procedure (1 Viewer)

[arvdot]

Hi there, I have managed to find a way to loop through a recordset as defined by the current reports record source and filter, and to read an invoice's email field and send that specific invoice to that email. The problem I have though is that the email client will open up (Eudora in this case) and will prepare the email completely, except it won't automatically send the email. I must hit send. Now, I'm not lazy, but I don't want to do this. This procedure really slows down the system and is unbearable to run at points as I still have to sit here and hit send everytime so I can't leave my desk.
Here is my code:

Private Sub Email_Click()

On Error Resume Next

'Find the recordset that appears to the user.
Dim strTo As String
Dim strTitle As String
Dim strMessage As String
Dim Query
Query = Left(Reports("ClosedRequests").RecordSource, Len(Reports("ClosedRequests").RecordSource) - 2) & " AND " & Reports("ClosedRequests").Filter
Dim RsMyRS As DAO.Recordset
Set RsMyRS = CurrentDb.OpenRecordset(Query)
Dim SRID
RsMyRS.MoveFirst

'Read address to email, then send an email based on the settings the user chose before opening the report. Settings
'Are in underlying textboxs on this report for reference.
Do While Not (RsMyRS.EOF)
SRID = RsMyRS![SR#]
RsMyRS.MoveNext
DoCmd.OpenReport "Invoice", acViewPreview, , "SRID=" & SRID
strTo = Reports("Invoice")![E-mail Address]
strTitle = Me!Subject & SRID
strMessage = Me!Body
DoCmd.SendObject acSendReport, "Invoice", acFormatPDF, strTo, , , strTitle, strMessage
DoCmd.Close acReport, "Invoice"
If MsgBox("Click when ready to send next.", vbOKOnly) <> vbOK Then Exit Sub
Loop

'Finish Off.
RsMyRS.Close
End Sub


Everything works perfectly. I just imagine I need one more line of code to completely automate the process so I don't have to be involved once it starts. If anyone knows any methods of accomplishing it that would help a lot! Thanks!

 

[KenHigg]

a. Maybe you can use sendkeys
b. Everything else failing maybe you can use Outlook where you can manipulate it from MS Access VBA...

 

[arvdot]

I figured it out. Thanks though. I kept searching on a bunch of other sites.

If anyone is curious as to the answer, simply set the parameter of [EditMessage] to False in the docmd.sendobject method.

 

[KenHigg]

Thanks for posting your solution. Now that you mentioned it I remember using that setting.

 

[akt01]

Arvdot –

Help. I am trying to do the exact same thing. But I am light years behind you

Can you help me understand your code

- How are you pulling the email etc from the table
- And how do you get the system to advance to the next record, to send the next email


I believe it is somewhere in here but its greek to me.

Dim RsMyRS As DAO.Recordset
Set RsMyRS = CurrentDb.OpenRecordset(Query)

Dim SRID
RsMyRS.MoveFirst

SRID = RsMyRS![SR#]
RsMyRS.MoveNext


is RsMyRS = the table name

thanks

 

[arvdot]

-akt01

Essentially what you are doing in the vba script is building a virtual table (2d array), this is how I picture it anyways. The DAO.Recordset is an object and I named mine rsmyrs. The next line
Set RsMYRS = CurrentDb.OpenRecordset(Query)
populates the recordset I created (fills in the empty virtual table with information). IF you look up early in my code, I declared the parameter Query as an sql statement. You can't really tell, but it is actually an sql statement as it parses together the report's current recordsource and filter properties to make a giant sql.

What I do next (now that I have my virtual table populated by whatever entries qualified under my query) is cycle through the recordset (virtual table) and open each Invoice that is attributed to it's unique SR# one by one. I then used vba to read the Invoice and find the email address that the customer listed by using the line
strTo = Reports("Invoice")![E-mail Address].

Next I email the invoice with a couple of constants that were set before the report was opened (like the subject and body), close the invoice, and move on to the next entry in my virtual table and repeat the process. - I move to the next entry with rsmyrs.movenext

Once I'm at the end of my recordset (virtual table) I exit the loop since Rsmyrs.EOF (end of file) parameter is true. The last line I have closes the virtual table.