A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Code to Email from Access



 
 
Thread Tools Display Modes
  #1  
Old July 9th, 2009, 03:24 PM posted to microsoft.public.access
clk[_2_]
external usenet poster
 
Posts: 26
Default Code to Email from Access

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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.