C
clk
Hello. I have code working in a database to email an invoice to a
customer. I was having two problems. One the invoice number for the
first record was being changed to the last invoice number after
running the code. I think I fixed that one by "capturing" the invoice
number first and then setting it back after it runs. The second
problem is if there is not a valid email address, the process fails
part way through. Is there any way to check for a valid email
addresses before starting code?
Any help is appreciated.
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim strDocName As String
Dim stLinkCriteria As String
Dim strsnap As String
Dim strpdf As Parameter
Dim strInvNumber As String
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("QryInvoiceEmail", dbOpenSnapshot)
strInvNumber = Me.InvNumber
Do Until rsEmail.EOF
Me.InvNumber = rsEmail!InvNumber
DoCmd.OutputTo acOutputReport, "rptinvoicesemail",
acFormatSNP, "c:\invoices\" & _
rsEmail!InvNumber & ".snp"
Dim blRet As Boolean
blRet = ConvertReportToPDF(, "c:\invoices\" & rsEmail!
InvNumber & ".snp", _
"c:\invoices\" & rsEmail!InvNumber & ".pdf", False,
False, 0, "", "", 0, 0)
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(rsEmail!EMail)
objOutlookRecip.Type = olTo
' Set the Subject, Body, and Importance of the message.
.Subject = "Invoice " & rsEmail!InvNumber
.Body = "Please see attached invoice." & Chr(13) & Chr
(13) & "If you have any questions, please call our office."
.Importance = olImportanceHigh 'High importance
' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add("c:\invoices
\" & rsEmail!InvNumber & ".pdf")
End If
' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
' Should we display the message before sending?
If DisplayMsg Then
.Display
Else
.Save
.Send
End If
End With
Set objOutlook = Nothing
rsEmail.MoveNext
Loop
Set MyDb = Nothing
Set rsEmail = Nothing
Me.InvNumber = strInvNumber
customer. I was having two problems. One the invoice number for the
first record was being changed to the last invoice number after
running the code. I think I fixed that one by "capturing" the invoice
number first and then setting it back after it runs. The second
problem is if there is not a valid email address, the process fails
part way through. Is there any way to check for a valid email
addresses before starting code?
Any help is appreciated.
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim strDocName As String
Dim stLinkCriteria As String
Dim strsnap As String
Dim strpdf As Parameter
Dim strInvNumber As String
Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("QryInvoiceEmail", dbOpenSnapshot)
strInvNumber = Me.InvNumber
Do Until rsEmail.EOF
Me.InvNumber = rsEmail!InvNumber
DoCmd.OutputTo acOutputReport, "rptinvoicesemail",
acFormatSNP, "c:\invoices\" & _
rsEmail!InvNumber & ".snp"
Dim blRet As Boolean
blRet = ConvertReportToPDF(, "c:\invoices\" & rsEmail!
InvNumber & ".snp", _
"c:\invoices\" & rsEmail!InvNumber & ".pdf", False,
False, 0, "", "", 0, 0)
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(rsEmail!EMail)
objOutlookRecip.Type = olTo
' Set the Subject, Body, and Importance of the message.
.Subject = "Invoice " & rsEmail!InvNumber
.Body = "Please see attached invoice." & Chr(13) & Chr
(13) & "If you have any questions, please call our office."
.Importance = olImportanceHigh 'High importance
' Add attachments to the message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add("c:\invoices
\" & rsEmail!InvNumber & ".pdf")
End If
' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
Next
' Should we display the message before sending?
If DisplayMsg Then
.Display
Else
.Save
.Send
End If
End With
Set objOutlook = Nothing
rsEmail.MoveNext
Loop
Set MyDb = Nothing
Set rsEmail = Nothing
Me.InvNumber = strInvNumber