By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,969 Members | 1,522 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,969 IT Pros & Developers. It's quick & easy.

To Auto send email in VBA, won't attach file

100+
P: 144
If I change the .Display to .Send I get Error '-2147221238 (8004010A) The item has been moved or deleted.

Its sending the email without the attachment. If I have it as .Display it works perfect, other than having to click the send button.

Also if I remove the .Display all together its the same as if it was .Send

So my guess is its sending the email before it has a chance to attach the file.

To solve this would I move
Expand|Select|Wrap|Line Numbers
  1. .Attachments.Add "N:\Inspect\New Reject Tag Database\Reports" _ 
  2. & "\" & strReportName & ".PDF"
Above the .send(.Display) section?

Below is the full code for sending this email.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2.  
  3. Dim strEMail As String
  4. Dim oOutlook As Object
  5. Dim oMail As Object
  6. Dim strAddr As String
  7. Dim MyDB As DAO.Database
  8. Dim rstEMail As DAO.Recordset
  9.  
  10. If Me.[REJECT AREA].Value = "Purchased" Then
  11.  
  12. Set oOutlook = CreateObject("Outlook.Application")
  13. Set oMail = oOutlook.CreateItem(0)
  14.  
  15. Dim strReportName As String
  16.  
  17. strReportName = Replace(Replace("REJECT TAG#|1 PN |2", "|1", Nz(" " & _
  18.  [REJECT TAG NUMBER], " ")), "|2", Nz([Part Number] & _
  19.  " " & [Descriptive Reason], " "))
  20.  
  21. Dim myReportWhere As String
  22.  
  23.  myReportWhere = "[REJECT TAG NUMBER] = " _
  24.     & [Forms]![Reject Tag Form]![REJECT TAG NUMBER]
  25.  
  26. DoCmd.OpenReport _
  27.  ReportName:="Reject Tag Report", _
  28.  View:=acViewPreview, _
  29.  WhereCondition:=myReportWhere, _
  30.  WindowMode:=acWindowNormal
  31.  
  32. Dim myOutPutFile As String
  33.  
  34. myOutPutFile = "N:\Inspect\New Reject Tag Database\Reports" & _
  35. "\" & strReportName & ".PDF"
  36.  
  37. DoCmd.OutputTo _
  38. Objecttype:=acOutputReport, _
  39.    Outputformat:=acFormatPDF, _
  40.    Outputfile:=myOutPutFile, _
  41.    AutoStart:=False, _
  42.    Outputquality:=acExportQualityPrint
  43.  
  44. DoCmd.Close _
  45.    Objecttype:=acReport, _
  46.    ObjectName:="Reject Tag Report", _
  47.    Save:=acSaveNo
  48.  
  49. 'Retrieve all E-Mail Addressess in tblEMailAddress
  50. Set MyDB = CurrentDb
  51. Set rstEMail = MyDB.OpenRecordset("Select * From tblEMailPurchased", _
  52. dbOpenSnapshot, dbOpenForwardOnly)
  53.  
  54. With rstEMail
  55.   Do While Not .EOF
  56.     'Build the Recipients String
  57.     strEMail = strEMail & ![EmailAddress] & ";"
  58.       .MoveNext
  59.   Loop
  60. End With
  61. '--------------------------------------------------
  62.  
  63. With oMail
  64.   .To = Left$(strEMail, Len(strEMail) - 1)
  65.   .Body = "Please review the attached Reject Tag for a Purchased Component."
  66.   .Subject = Replace(Replace("REJECT TAG#|1 PN |2", "|1", Nz(" " & _
  67.  [REJECT TAG NUMBER], " ")), "|2", Nz([Part Number] & _
  68.  " " & [Descriptive Reason], " "))
  69.     .Display
  70.   .Attachments.Add "N:\Inspect\New Reject Tag Database\Reports" & "\" & strReportName & ".PDF"
  71.  
  72. End With
  73.  
  74. Set oMail = Nothing
  75. Set oOutlook = Nothing
  76.  
  77. rstEMail.Close
  78. Set rstEMail = Nothing
  79.  
  80. Dim aFile As String
  81. aFile = "N:\Inspect\New Reject Tag Database\Reports" & "\" & strReportName & ".PDF"
  82. If Len(Dir$(aFile)) > 0 Then
  83.      Kill aFile
  84.     End If
  85.     End If
  86. End Sub
Thanks for any tips or solutions as I'm still learning thanks to everyone here I have gotten so much better.
2 Weeks Ago #1

✓ answered by ADezii

Try reversing Lines 69 & 70 (Attach the File prior to Sending the E-Mail).

Share this Question
Share on Google+
19 Replies


ADezii
Expert 5K+
P: 8,619
Try reversing Lines 69 & 70 (Attach the File prior to Sending the E-Mail).
2 Weeks Ago #2

twinnyfo
Expert Mod 2.5K+
P: 3,184
DJ,

So you don't have to reinvent the wheel on this one, either, you may want to take a look at this article on sending e-mail via Outlook. It makes sending e-mails more simple and prevents you from re-establishing Outlook objects in your code every time you want to send some form of e-mail.

Hope this hepps!

(But I also noticed that you've already seen that article).
2 Weeks Ago #3

100+
P: 144
Yeah I did read that. My issue is I'm still real green and a little lost on how to use this for my purposes. At the moment I just use the code above through copy past and change the info that needs to change for that particular email.

I'm betting once I have time to truly dissect the code in that article and play with it, it will make my life much easier. I just don't think I'm quit there yet.
2 Weeks Ago #4

twinnyfo
Expert Mod 2.5K+
P: 3,184
I would recommend you try to dissect the process earlier rather than later. Your new code would be:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2.     Dim strEMail        As String
  3.     Dim strSubject      As String
  4.     Dim strBody         As String
  5.     Dim MyDB            As DAO.Database
  6.     Dim rstEMail        As DAO.Recordset
  7.     Dim strReportName   As String
  8.     Dim myReportWhere   As String
  9.     Dim myOutPutFile    As String
  10.     Dim aFile           As String
  11.  
  12.     If Me.[REJECT AREA].Value = "Purchased" Then
  13.  
  14.         strReportName = _
  15.             Replace(Replace("REJECT TAG#|1 PN |2", "|1", Nz(" " & _
  16.             [REJECT TAG NUMBER], " ")), "|2", Nz([Part Number] & _
  17.             " " & [Descriptive Reason], " "))
  18.         myReportWhere = "[REJECT TAG NUMBER] = " & _
  19.             [Forms]![Reject Tag Form]![REJECT TAG NUMBER]
  20.  
  21.         DoCmd.OpenReport _
  22.             ReportName:="Reject Tag Report", _
  23.             View:=acViewPreview, _
  24.             WhereCondition:=myReportWhere, _
  25.             WindowMode:=acWindowNormal
  26.  
  27.         myOutPutFile = "N:\Inspect\New Reject Tag Database\Reports" & _
  28.             "\" & strReportName & ".PDF"
  29.  
  30.         DoCmd.OutputTo _
  31.             Objecttype:=acOutputReport, _
  32.             Outputformat:=acFormatPDF, _
  33.             Outputfile:=myOutPutFile, _
  34.             AutoStart:=False, _
  35.             Outputquality:=acExportQualityPrint
  36.  
  37.         DoCmd.Close _
  38.             Objecttype:=acReport, _
  39.             ObjectName:="Reject Tag Report", _
  40.             Save:=acSaveNo
  41.  
  42.         'Retrieve all E-Mail Addressess in tblEMailAddress
  43.         Set MyDB = CurrentDb
  44.         Set rstEMail = MyDB.OpenRecordset("Select * From tblEMailPurchased", _
  45.             dbOpenSnapshot, dbOpenForwardOnly)
  46.  
  47.         With rstEMail
  48.             If Not (.BOF And .EOF) Then
  49.                 Call .MoveFirst
  50.                 Do While Not .EOF
  51.                     'Build the Recipients String
  52.                     strEMail = ""
  53.                     strEMail = _
  54.                         strEMail & _
  55.                         IIf(strEMail = "", _
  56.                             "", _
  57.                             ";") & _
  58.                         !EmailAddress
  59.                     Call .MoveNext
  60.                 Loop
  61.             End If
  62.             Call .Close
  63.         End With
  64.         Call MyDB.Close
  65.         Set rstEMail = Nothing
  66.         Set MyDB = Nothing
  67.  
  68.         strBody = _
  69.             "Please review the attached Reject Tag for a Purchased Component."
  70.         strSubject = _
  71.             Replace(Replace("REJECT TAG#|1 PN |2", "|1", Nz(" " & _
  72.             [REJECT TAG NUMBER], " ")), "|2", Nz([Part Number] & _
  73.             " " & [Descriptive Reason], " "))
  74.         strAtch = _
  75.             "N:\Inspect\New Reject Tag Database\Reports" & _
  76.             "\" & strReportName & ".PDF"
  77.  
  78.         Call SendAnEMail(olSendTo:=strEMail, _
  79.                          olSubject:=strSubject, _
  80.                          olEMailBody:=strBody, _
  81.                          olAtchs:=strAtch, _
  82.                          olDisplay:=True, _
  83.                          SendAsHTML:=False)
  84.  
  85.         aFile = _
  86.             "N:\Inspect\New Reject Tag Database\Reports" & _
  87.             "\" & strReportName & ".PDF"
  88.         If Len(Dir$(aFile)) > 0 Then _
  89.              Call Kill(PathName:=aFile)
  90.     End If
  91. End Sub
Pay special attention to lines 68-82. That is all you need to send an e-mail! Why continue to work through figuring out how to declare Outlook objects when there is no need. Work smarter, not harder!

A little advice.... Try to keep all your nested statements indented properly. This allows you to visually see which statements belong with which.

Also, It is usually a good practice to declare all your variables at the beginning of your procedures. This allows you to quickly find them (or change them) if need be.

Hope this hepps!
2 Weeks Ago #5

NeoPa
Expert Mod 15k+
P: 31,347
Nice work everyone.

Very simple solution provided but also some great advice for DJ to help them along on their journey.

All good stuff :-)
2 Weeks Ago #6

100+
P: 144
twinnyfo,

Not sure if I should have started a new thread or not?? but,

I took the code from above and replaced it with the code above(Backed up the old one first) and also added the public functions of SendAnEmail and FileExists. I'm getting compile errors on
Expand|Select|Wrap|Line Numbers
  1. Call SendAnEmail
Compile error: Expected variable or procedure, not module.
What did I miss?

I also Can not Compile The Public function of SendAnEmail. It gets tripped up on:

Expand|Select|Wrap|Line Numbers
  1. On Error GoTo EH
  2.     Dim olApp       As Outlook.Application
  3.     Dim olMail      As Outlook.MailItem
  4.     Dim strArray()  As String
  5.     Dim intAtch     As Integer
  6.  
  7.     Set olApp = CreateObject("Outlook.Application")
  8.     Set olMail = olApp.CreateItem(olMailItem)
Says Compile Error: User-Defined type not defined

Whats Wrong here?
2 Weeks Ago #7

100+
P: 144
Nevermind, I'm an idiot...For got the reference Library part.
2 Weeks Ago #8

100+
P: 144
After the reference to Microsoft was added, the only part that hangs on compiling is the
Expand|Select|Wrap|Line Numbers
  1. Call SendAnEmail(olSendTo:=strEMail, _
  2.                          olEMailBody:=strBody, _
  3.                          olAtchs:=strAtch, _
  4.                          olDisplay:=True, _
  5.                          SendAsHTML:=True)
2 Weeks Ago #9

twinnyfo
Expert Mod 2.5K+
P: 3,184
What is the error you are receiving? That's where we have to start.
2 Weeks Ago #10

NeoPa
Expert Mod 15k+
P: 31,347
You should also post all the relevant code so we can see the context.
2 Weeks Ago #11

100+
P: 144
Expand|Select|Wrap|Line Numbers
  1. Call SendAnEmail
is where it hangs up. Error is: Compile Error: Expected variable or procedure, not module

I created a public Function in a module called SendEmail and here is the code.

Expand|Select|Wrap|Line Numbers
  1. Public Function SendAnEmail(olSendTo As String, _
  2.                             olSubject As String, _
  3.                             olEMailBody As String, _
  4.                             olDisplay As Boolean, _
  5.                    Optional olCCLine As String, _
  6.                    Optional olBCCLine As String, _
  7.                    Optional olOnBehalfOf As String, _
  8.                    Optional olAtchs As String, _
  9.                    Optional SendAsHTML As Boolean) As Boolean
  10. On Error GoTo EH
  11.     Dim olApp       As Outlook.Application
  12.     Dim olMail      As Outlook.MailItem
  13.     Dim strArray()  As String
  14.     Dim intAtch     As Integer
  15.  
  16.     Set olApp = CreateObject("Outlook.Application")
  17.     Set olMail = olApp.CreateItem(olMailItem)
  18.     With olMail
  19.         .To = olSendTo
  20.         .Subject = olSubject
  21.  
  22.         If SendAsHTML Then
  23.             .BodyFormat = olFormatHTML
  24.             .HTMLBody = olEMailBody
  25.         Else
  26.             .Body = olEMailBody
  27.         End If
  28.  
  29.         .CC = olCCLine
  30.         .BCC = olBCCLine
  31.         .SentOnBehalfOfName = olOnBehalfOf
  32.         strArray = Split(olAtchs, "%Atch")
  33.  
  34.         For intAtch = 0 To UBound(strArray)
  35.             If FileExists(strArray(intAtch)) Then _
  36.                 .Attachments.Add strArray(intAtch)
  37.         Next intAtch
  38.  
  39.         If olDisplay Then
  40.             .Display
  41.         Else
  42.             .Send
  43.         End If
  44.  
  45.     End With
  46.     Set olMail = Nothing
  47.     Set olApp = Nothing
  48.  
  49.     SendAnEmail = True
  50.  
  51.     Exit Function
  52. EH:
  53.     MsgBox "There was an error generating the E-Mail!" & vbCrLf & vbCrLf & _
  54.         "Error: " & Err.Number & vbCrLf & _
  55.         "Description: " & Err.Description & vbCrLf & vbCrLf & _
  56.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  57.     SendAnEmail = False
  58.     Exit Function
  59. End Function
  60.  
  61.  
  62. Public Function FileExists(ByVal strFile As String, _
  63.                            Optional bFindFolders As Boolean) _
  64.                            As Boolean
  65. 'Purpose:   Return True if the file exists, even if it is hidden.
  66. 'Arguments: strFile: File name to look for. Current directory searched if
  67. '                    no path included.
  68. '           bFindFolders: If strFile is a folder, FileExists() returns False
  69. '                         unless this argument is True.
  70. 'Note:      Does not look inside subdirectories for the file.
  71. 'Author:    Allen Browne. http://allenbrowne.com June, 2006.
  72.     Dim lngAttributes As Long
  73.  
  74.     If Not (IsNull(strFile) Or strFile = "") Then
  75.         'Include read-only files, hidden files, system files.
  76.         lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
  77.  
  78.         If bFindFolders Then
  79.             'Include folders as well.
  80.             lngAttributes = (lngAttributes Or vbDirectory)
  81.         Else
  82.             'Strip any trailing slash, so Dir does not look inside the folder.
  83.             Do While Right$(strFile, 1) = "\"
  84.                 strFile = Left$(strFile, Len(strFile) - 1)
  85.             Loop
  86.         End If
  87.         'If Dir() returns something, the file exists.
  88.         On Error Resume Next
  89.         FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
  90.     Else
  91.         FileExists = False
  92.     End If
  93.  
  94. End Function
2 Weeks Ago #12

twinnyfo
Expert Mod 2.5K+
P: 3,184
Did you name the Module "SendAnEmail" as folks often want to do. This goes back to having a good naming convention. Rename that Module "modSendAnEmail".

Try that.
2 Weeks Ago #13

100+
P: 144
I did name it "SendAnEmail". Renamed it to "modSendAnEmail" and still will not compile. If I comment (') out that section It compiles the rest of the code. I do however get a different Error.

Compile Error: Argument not optional

Expand|Select|Wrap|Line Numbers
  1. Call SendAnEmail(olSendTo:=strEMail, _
  2.                          olEMailBody:=strBody, _
  3.                          olAtchs:=strAtch, _
  4.                          olDisplay:=False, _
  5.                          SendAsHTML:=True)
2 Weeks Ago #14

twinnyfo
Expert Mod 2.5K+
P: 3,184
I forgot to add the subject line: see Line 79 from Post #5.

That'll do it.
2 Weeks Ago #15

100+
P: 144
That did it, it has completely compiled. Now I'll test the functionality of it.
2 Weeks Ago #16

100+
P: 144
Works perfectly...Thanks so much for the help and all the tips and tricks.
2 Weeks Ago #17

twinnyfo
Expert Mod 2.5K+
P: 3,184
Great news! Let us know if there is anythings else!
2 Weeks Ago #18

NeoPa
Expert Mod 15k+
P: 31,347
@DJ.
When you get an error message like Argument not Optional, then look at the list of arguments in the procedure being called and compare that with the arguments in the call to the procedure. Focus on those which are not declared as Optional of course. For it to compile all the non-optional arguments must be passed in the call, so whichever aren't will be the cause of the failure to compile.

In this case clearly, that was the olSubject argument.
2 Weeks Ago #19

100+
P: 144
Thanks NeoPa, I was thinking that, but was having a hard time figuring out what was missing.Sometimes the obvious things are the hardest things to find. Did not notice the subject part was even missing.

Then once Twinnyfo posted and I looked through it, it was like duh.
2 Weeks Ago #20

Post your reply

Sign in to post your reply or Sign up for a free account.