Email Invoice to Spefic Client

[clk]

I have a button that generates all invoices where the client prefers
to be emailed their invoice. I now need to set up code to email each
invoice to a specific customer. For example, I may generate 10
invoices for the day all marked to be emailed. I want to click a
button that will create and email all 10 invoices to their respective
contacts stored in the tblcustomer.

Here is the code I have so far which generates one invoice. Not sure
how it is going to work when there is multiple invoices created. Each
invoice needing to be sent to a separate person.

Private Sub Command17_Click()
On Error Resume Next

If MsgBox("This will send invoices to customers marked to email.
Continue?", _
vbExclamation + vbYesNoCancel) = vbYes Then

Dim strMailList As String
Dim rs As Recordset
sqlStmt = "select * From qryEmailCustomers where [CustomerID]='" &
[CustomerID] & "'"


'Set rs = CurrentDb.OpenRecordset("qryEmailCustomers", dbOpenDynaset)
Set rs = CurrentDb.OpenRecordset(sqlStmt, dbOpenDynaset)
With rs
.MoveFirst

Do
strMailList = strMailList & !EMail & ";"
strMailList = strMaiList & !EMail
.MoveNext
Loop Until .EOF

strMailList = Left(strMailList, Len(strMailList) - 1)
.Close
End With



DoCmd.SendObject acSendReport, "rptInvoicesEmail", acFormatSNP,
strMailList, , , "INVOICE" _
, , True



End If

Set rs = Nothing

End Sub

This code was gathered on the forums and I am not sure who originally
wrote it.

Any help would be greatly appreciated.

~ Carrie

 

[Arvin Meyer MVP]

Within the loop, you must run the Invoice report for each individual
customer in qryEmailCustomers, then create a PDF, then email it.

I use Steve Arbaugh's PDF & Mail creator class:

http://www.groupacg.com/

with Win2PDF

http://www.win2pdf.com

You could also try using Stephen Lebans' PDF creator, which is free:

http://www.lebans.com/reporttopdf.htm

 

[clk]

Within the loop, you must run the Invoice report for each individual
customer in qryEmailCustomers, then create a PDF, then email it.

I use Steve Arbaugh's PDF & Mail creator class:

http://www.groupacg.com/

with Win2PDF

http://www.win2pdf.com

You could also try using Stephen Lebans' PDF creator, which is free:

http://www.lebans.com/reporttopdf.htm
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

I have a button that generates all invoices where the client prefers
to be emailed their invoice.  I now need to set up code to email each
invoice to a specific customer.  For example, I may generate 10
invoices for the day all marked to be emailed.  I want to click a
button that will create and email all 10 invoices to their respective
contacts stored in the tblcustomer.

Here is the code I have so far which generates one invoice.  Not sure
how it is going to work when there is multiple invoices created.  Each
invoice needing to be sent to a separate person.

Private Sub Command17_Click()
On Error Resume Next

If MsgBox("This will send invoices to customers marked to email.
Continue?", _
          vbExclamation + vbYesNoCancel) = vbYes Then

Dim strMailList As String
Dim rs As Recordset
sqlStmt = "select * From qryEmailCustomers where [CustomerID]='" &
[CustomerID] & "'"

'Set rs = CurrentDb.OpenRecordset("qryEmailCustomers", dbOpenDynaset)
Set rs = CurrentDb.OpenRecordset(sqlStmt, dbOpenDynaset)
With rs
 .MoveFirst

   Do
   strMailList = strMailList & !EMail & ";"
   strMailList = strMaiList & !EMail
       .MoveNext
   Loop Until .EOF

     strMailList = Left(strMailList, Len(strMailList) - 1)
       .Close
End With

 DoCmd.SendObject acSendReport, "rptInvoicesEmail", acFormatSNP,
strMailList, , , "INVOICE" _
 , , True

End If

Set rs = Nothing

End Sub

This code was gathered on the forums and I am not sure who originally
wrote it.

Any help would be greatly appreciated.

~ Carrie- Hide quoted text -

- Show quoted text -

Thank you for the help. I was wondering if that would do it. I am
not sure how to specify the criteria for each. It needs to find the
correct email address for each report.

With rs
.MoveFirst

Do
strMailList = strMailList & !EMail & ";"
strMailList = strMaiList & !EMail
.MoveNext
DoCmd.SendObject acSendReport, "rptInvoicesEmail", acFormatSNP,
strMailList, , , "Your invoice" _
, , True

Loop Until .EOF
strMailList = Left(strMailList, Len(strMailList) - 1)
.Close
End With

I changed the code to the above and now it creates a separate email
for each person that has an email address. Getting there. But I need
to only send the invoice to the company that is being invoiced. :.)
I am not sure how to write the criteria line.

Any additional help would be appreciated.

 

[Arvin Meyer MVP]

I use a query which supplies the CustomerID for both the email address and
the report. One of the things that the Mail creator class does is to marry
them properly. In my case, I am sending POs, not Invoices but the code is
very similar. Here's a snippet:

This grabs the email address from a query:

Set rstReports = CurrentDb.OpenRecordset("Select ContractorID, PONumber,
Email From qryPrintPOHeader", dbOpenSnapshot)

Set objPDF = New PDFClass

Do Until rstReports.EOF ' Loop until End of File

If Len(rstReports!Email & vbNullString) = 0 Then
strEmail = "(e-mail address removed)"
Else
strEmail = rstReports!Email
End If

' This creates the PDF from the report:

With objPDF
.PDFEngine = PDFENGINE_WIN2PDF
.ReportName = "rptPurchaseOrder"
.ReportWhere = "[PONumber] = " & rstReports!PONumber
.OutputFile = strCurFileName
.PrintImage
lngReturn = .Result
End With

' Then I send the email and go to the next record.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Within the loop, you must run the Invoice report for each individual
customer in qryEmailCustomers, then create a PDF, then email it.

I use Steve Arbaugh's PDF & Mail creator class:

http://www.groupacg.com/

with Win2PDF

http://www.win2pdf.com

You could also try using Stephen Lebans' PDF creator, which is free:

http://www.lebans.com/reporttopdf.htm
--
Arvin Meyer, MCP,
MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

I have a button that generates all invoices where the client prefers
to be emailed their invoice. I now need to set up code to email each
invoice to a specific customer. For example, I may generate 10
invoices for the day all marked to be emailed. I want to click a
button that will create and email all 10 invoices to their respective
contacts stored in the tblcustomer.

Here is the code I have so far which generates one invoice. Not sure
how it is going to work when there is multiple invoices created. Each
invoice needing to be sent to a separate person.

Private Sub Command17_Click()
On Error Resume Next

If MsgBox("This will send invoices to customers marked to email.
Continue?", _
vbExclamation + vbYesNoCancel) = vbYes Then

Dim strMailList As String
Dim rs As Recordset
sqlStmt = "select * From qryEmailCustomers where [CustomerID]='" &
[CustomerID] & "'"

'Set rs = CurrentDb.OpenRecordset("qryEmailCustomers", dbOpenDynaset)
Set rs = CurrentDb.OpenRecordset(sqlStmt, dbOpenDynaset)
With rs
.MoveFirst

Do
strMailList = strMailList & !EMail & ";"
strMailList = strMaiList & !EMail
.MoveNext
Loop Until .EOF

strMailList = Left(strMailList, Len(strMailList) - 1)
.Close
End With

DoCmd.SendObject acSendReport, "rptInvoicesEmail", acFormatSNP,
strMailList, , , "INVOICE" _
, , True

End If

Set rs = Nothing

End Sub

This code was gathered on the forums and I am not sure who originally
wrote it.

Any help would be greatly appreciated.

~ Carrie- Hide quoted text -

- Show quoted text -

Thank you for the help. I was wondering if that would do it. I am
not sure how to specify the criteria for each. It needs to find the
correct email address for each report.

With rs
.MoveFirst

Do
strMailList = strMailList & !EMail & ";"
strMailList = strMaiList & !EMail
.MoveNext
DoCmd.SendObject acSendReport, "rptInvoicesEmail", acFormatSNP,
strMailList, , , "Your invoice" _
, , True

Loop Until .EOF
strMailList = Left(strMailList, Len(strMailList) - 1)
.Close
End With

I changed the code to the above and now it creates a separate email
for each person that has an email address. Getting there. But I need
to only send the invoice to the company that is being invoiced. :.)
I am not sure how to write the criteria line.

Any additional help would be appreciated.

 

[dejuarian]

I have a button that generates all invoices where the client prefers
to be emailed their invoice. I now need to set up code to email each
invoice to a specific customer. For example, I may generate 10
invoices for the day all marked to be emailed. I want to click a
button that will create and email all 10 invoices to their respective
contacts stored in the tblcustomer.

Here is the code I have so far which generates one invoice. Not sure
how it is going to work when there is multiple invoices created. Each
invoice needing to be sent to a separate person.

Private Sub Command17_Click()
On Error Resume Next

If MsgBox("This will send invoices to customers marked to email.
Continue?", _
vbExclamation + vbYesNoCancel) = vbYes Then

Dim strMailList As String
Dim rs As Recordset
sqlStmt = "select * From qryEmailCustomers where [CustomerID]='" &
[CustomerID] & "'"


'Set rs = CurrentDb.OpenRecordset("qryEmailCustomers", dbOpenDynaset)
Set rs = CurrentDb.OpenRecordset(sqlStmt, dbOpenDynaset)
With rs
.MoveFirst

Do
strMailList = strMailList & !EMail & ";"
strMailList = strMaiList & !EMail
.MoveNext
Loop Until .EOF

strMailList = Left(strMailList, Len(strMailList) - 1)
.Close
End With



DoCmd.SendObject acSendReport, "rptInvoicesEmail", acFormatSNP,
strMailList, , , "INVOICE" _
, , True



End If

Set rs = Nothing

End Sub

This code was gathered on the forums and I am not sure who originally
wrote it.

Any help would be greatly appreciated.

~ Carrie