473,322 Members | 1,671 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

DJRhino1175
221 128KB
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.
Jun 4 '19 #1

✓ answered by ADezii

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

19 1578
ADezii
8,834 Expert 8TB
Try reversing Lines 69 & 70 (Attach the File prior to Sending the E-Mail).
Jun 4 '19 #2
twinnyfo
3,653 Expert Mod 2GB
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).
Jun 4 '19 #3
DJRhino1175
221 128KB
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.
Jun 4 '19 #4
twinnyfo
3,653 Expert Mod 2GB
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!
Jun 4 '19 #5
NeoPa
32,556 Expert Mod 16PB
Nice work everyone.

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

All good stuff :-)
Jun 4 '19 #6
DJRhino1175
221 128KB
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?
Jun 4 '19 #7
DJRhino1175
221 128KB
Nevermind, I'm an idiot...For got the reference Library part.
Jun 4 '19 #8
DJRhino1175
221 128KB
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)
Jun 4 '19 #9
twinnyfo
3,653 Expert Mod 2GB
What is the error you are receiving? That's where we have to start.
Jun 4 '19 #10
NeoPa
32,556 Expert Mod 16PB
You should also post all the relevant code so we can see the context.
Jun 5 '19 #11
DJRhino1175
221 128KB
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
Jun 5 '19 #12
twinnyfo
3,653 Expert Mod 2GB
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.
Jun 5 '19 #13
DJRhino1175
221 128KB
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)
Jun 5 '19 #14
twinnyfo
3,653 Expert Mod 2GB
I forgot to add the subject line: see Line 79 from Post #5.

That'll do it.
Jun 5 '19 #15
DJRhino1175
221 128KB
That did it, it has completely compiled. Now I'll test the functionality of it.
Jun 5 '19 #16
DJRhino1175
221 128KB
Works perfectly...Thanks so much for the help and all the tips and tricks.
Jun 5 '19 #17
twinnyfo
3,653 Expert Mod 2GB
Great news! Let us know if there is anythings else!
Jun 5 '19 #18
NeoPa
32,556 Expert Mod 16PB
@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.
Jun 5 '19 #19
DJRhino1175
221 128KB
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.
Jun 6 '19 #20

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

Similar topics

2
by: Landley | last post by:
Hello, Is there a way of creating an email, attaching a file and sending using the client's default mail client? I am looking for a none email client specific solution that does not involve...
13
by: bmurphy | last post by:
Last week after much searching, I found the answer to my problem in this newsgroup. I can't find the thread from which I got my solution, but I wanted to report back what worked. When the site...
3
by: =?Utf-8?B?SHVnaA==?= | last post by:
Hi There, I use follow code to send email inside VB.NET 2005. It does not work well. Error message of "Failure sending email" would occue. However, email was sent out sometimes. I am confused...
3
by: =?Utf-8?B?YmVueQ==?= | last post by:
See the ASP code below in order to send an email. How can I attach a file to this email please? set objMailer = Server.CreateObject("CDONTS.Newmail") objMailer.From = "anySender@123.com"...
4
by: lyealain | last post by:
hi .. thanks for the effort to read this.. thanks.. can u give me idea on building an auto triggered email system in asp/VB ..every time when it reaches the time i specify..the sytem will auto...
7
by: RRsonawane11 | last post by:
Hello, Can anyone know how to write asp code to attach one file and email it to a specified email address. The attach file is the client choose from their Local Harddisk, just like we ...
2
by: Boki | last post by:
Hi All, I want to create a program that auto send out email ( not email bomb ) to myself. I tried the MAPI, but it will show a notiifcation message, user need to confirm that. My target is...
2
by: MyWaterloo | last post by:
I have a database that I’ve created for creating/printing/sending purchase orders. It works great! Long live Access! Although, I have a slight problem with the sending part. I have a table for my...
12
by: qfchen | last post by:
Hi I'd like to implement in VB2005 to send a email and attech a file inside the email. Anyone knows how to do it? Thanks.
0
by: Mike Massaro | last post by:
Hello everyone, I'm new to PHP and creating an advertising website for massage therapists. On the profile page I'm creating a button so anyone can click on to send the advertiser an email. I...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.