Emailing attachments - conditional (WHERE) reports. Just missing some code!

[gangel]

Hey everyone so below is my code and im getting some errors.

Im ALMOST there!

Worked this one out with just me and Mr Google.

I havent worked with outside applications before so this is testing me a bit.

Pretty much:


I have a list of people to get emails
I export a pdf of THEIR version of the report (using filter and global DIM) (TY Allen browne)
I then import that into a mailitem and send

... Now the loop is failing...

Private Sub Command70_Click()Dim RSEmail As DAO.Recordset
Dim RBID As Integer


Dim EAdd As String
Dim ReconID As Long
Dim DateTmp As String
Dim FileName As String
Dim ESubject As String
Dim EBody As String
Dim ReconReport As Report
Dim FilePath As String
Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem


If oOutlook Is Nothing Then
Set oOutlook = New Outlook.Application
End If




RBID = DLookup("SettingValue", "Settings", "[SettingID] = 4")
Set RSEmail = CurrentDb.OpenRecordset("SELECT * FROM EmailRecons WHERE [ReconBatchID] = " & RBID)
Set oEmailItem = oOutlook.CreateItem(olMailItem)


RSEmail.MoveFirst


Do Until RSEmail.EOF


ReconID = RSEmail!ReconcilID
DateTmp = Format(RSEmail!ReconDate, "ddmmyy")
FileName = RSEmail!ChemShortCode & DateTmp
FilePath = "S:\PPSExport\" & FileName & ".pdf"
OutputString = "[ReconcilID]=" & ReconID
DoCmd.OutputTo acOutputReport, "RptReconcilSingle", acFormatPDF, FilePath




EAdd = RSEmail!EmailAdd


ESubject = "PPS Statement - " & Format(RSEmail!ReconDate, "dd/mm/yy")


EBody = "Attached is your E-Statement from Pharmaceutical Pricing Service." & vbCrLf & "Regards," & vbCrLf & "Ian Crawford" & vbCrLf & "9382 4069"






With oEmailItem
    .to = EAdd
    .Subject = ESubject
    .Attachments.add FilePath
    .Body = EBody
    .Send
End With










RSEmail.MoveNext

Loop



Set oEmailItem = Nothing
Set oOutlook = Nothing
Kill FilePath
End Sub

It sends ONE email then the second freezes on .to (under mail item) saying "The item has been moved or deleted"

Is it going too fast and getting confused?

Also i need to delete all files in S:\PPSExports after this is completed... is there an easy way to do this or do i do a "kill loop"

Gangel

[pbaldy]

You need to set the item inside the loop, not before.

[gangel]

lol i was just posting back!

Thanks for that! yes i should have the create item after the do until!

Amazing, its working!!

lol Saves my business writing 150 letters every week!

[pbaldy]

Glad you got it working!