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 - .Attachments.Add "N:\Inspect\New Reject Tag Database\Reports" _
-
& "\" & strReportName & ".PDF"
Above the .send(.Display) section?
Below is the full code for sending this email. - Private Sub Form_Close()
-
-
Dim strEMail As String
-
Dim oOutlook As Object
-
Dim oMail As Object
-
Dim strAddr As String
-
Dim MyDB As DAO.Database
-
Dim rstEMail As DAO.Recordset
-
-
If Me.[REJECT AREA].Value = "Purchased" Then
-
-
Set oOutlook = CreateObject("Outlook.Application")
-
Set oMail = oOutlook.CreateItem(0)
-
-
Dim strReportName As String
-
-
strReportName = Replace(Replace("REJECT TAG#|1 PN |2", "|1", Nz(" " & _
-
[REJECT TAG NUMBER], " ")), "|2", Nz([Part Number] & _
-
" " & [Descriptive Reason], " "))
-
-
Dim myReportWhere As String
-
-
myReportWhere = "[REJECT TAG NUMBER] = " _
-
& [Forms]![Reject Tag Form]![REJECT TAG NUMBER]
-
-
DoCmd.OpenReport _
-
ReportName:="Reject Tag Report", _
-
View:=acViewPreview, _
-
WhereCondition:=myReportWhere, _
-
WindowMode:=acWindowNormal
-
-
Dim myOutPutFile As String
-
-
myOutPutFile = "N:\Inspect\New Reject Tag Database\Reports" & _
-
"\" & strReportName & ".PDF"
-
-
DoCmd.OutputTo _
-
Objecttype:=acOutputReport, _
-
Outputformat:=acFormatPDF, _
-
Outputfile:=myOutPutFile, _
-
AutoStart:=False, _
-
Outputquality:=acExportQualityPrint
-
-
DoCmd.Close _
-
Objecttype:=acReport, _
-
ObjectName:="Reject Tag Report", _
-
Save:=acSaveNo
-
-
'Retrieve all E-Mail Addressess in tblEMailAddress
-
Set MyDB = CurrentDb
-
Set rstEMail = MyDB.OpenRecordset("Select * From tblEMailPurchased", _
-
dbOpenSnapshot, dbOpenForwardOnly)
-
-
With rstEMail
-
Do While Not .EOF
-
'Build the Recipients String
-
strEMail = strEMail & ![EmailAddress] & ";"
-
.MoveNext
-
Loop
-
End With
-
'--------------------------------------------------
-
-
With oMail
-
.To = Left$(strEMail, Len(strEMail) - 1)
-
.Body = "Please review the attached Reject Tag for a Purchased Component."
-
.Subject = Replace(Replace("REJECT TAG#|1 PN |2", "|1", Nz(" " & _
-
[REJECT TAG NUMBER], " ")), "|2", Nz([Part Number] & _
-
" " & [Descriptive Reason], " "))
-
.Display
-
.Attachments.Add "N:\Inspect\New Reject Tag Database\Reports" & "\" & strReportName & ".PDF"
-
-
End With
-
-
Set oMail = Nothing
-
Set oOutlook = Nothing
-
-
rstEMail.Close
-
Set rstEMail = Nothing
-
-
Dim aFile As String
-
aFile = "N:\Inspect\New Reject Tag Database\Reports" & "\" & strReportName & ".PDF"
-
If Len(Dir$(aFile)) > 0 Then
-
Kill aFile
-
End If
-
End If
-
End Sub
Thanks for any tips or solutions as I'm still learning thanks to everyone here I have gotten so much better.
Try reversing Lines 69 & 70 (Attach the File prior to Sending the E-Mail).
19 1578
Try reversing Lines 69 & 70 (Attach the File prior to Sending the E-Mail).
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).
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.
I would recommend you try to dissect the process earlier rather than later. Your new code would be: - Private Sub Form_Close()
-
Dim strEMail As String
-
Dim strSubject As String
-
Dim strBody As String
-
Dim MyDB As DAO.Database
-
Dim rstEMail As DAO.Recordset
-
Dim strReportName As String
-
Dim myReportWhere As String
-
Dim myOutPutFile As String
-
Dim aFile As String
-
-
If Me.[REJECT AREA].Value = "Purchased" Then
-
-
strReportName = _
-
Replace(Replace("REJECT TAG#|1 PN |2", "|1", Nz(" " & _
-
[REJECT TAG NUMBER], " ")), "|2", Nz([Part Number] & _
-
" " & [Descriptive Reason], " "))
-
myReportWhere = "[REJECT TAG NUMBER] = " & _
-
[Forms]![Reject Tag Form]![REJECT TAG NUMBER]
-
-
DoCmd.OpenReport _
-
ReportName:="Reject Tag Report", _
-
View:=acViewPreview, _
-
WhereCondition:=myReportWhere, _
-
WindowMode:=acWindowNormal
-
-
myOutPutFile = "N:\Inspect\New Reject Tag Database\Reports" & _
-
"\" & strReportName & ".PDF"
-
-
DoCmd.OutputTo _
-
Objecttype:=acOutputReport, _
-
Outputformat:=acFormatPDF, _
-
Outputfile:=myOutPutFile, _
-
AutoStart:=False, _
-
Outputquality:=acExportQualityPrint
-
-
DoCmd.Close _
-
Objecttype:=acReport, _
-
ObjectName:="Reject Tag Report", _
-
Save:=acSaveNo
-
-
'Retrieve all E-Mail Addressess in tblEMailAddress
-
Set MyDB = CurrentDb
-
Set rstEMail = MyDB.OpenRecordset("Select * From tblEMailPurchased", _
-
dbOpenSnapshot, dbOpenForwardOnly)
-
-
With rstEMail
-
If Not (.BOF And .EOF) Then
-
Call .MoveFirst
-
Do While Not .EOF
-
'Build the Recipients String
-
strEMail = ""
-
strEMail = _
-
strEMail & _
-
IIf(strEMail = "", _
-
"", _
-
";") & _
-
!EmailAddress
-
Call .MoveNext
-
Loop
-
End If
-
Call .Close
-
End With
-
Call MyDB.Close
-
Set rstEMail = Nothing
-
Set MyDB = Nothing
-
-
strBody = _
-
"Please review the attached Reject Tag for a Purchased Component."
-
strSubject = _
-
Replace(Replace("REJECT TAG#|1 PN |2", "|1", Nz(" " & _
-
[REJECT TAG NUMBER], " ")), "|2", Nz([Part Number] & _
-
" " & [Descriptive Reason], " "))
-
strAtch = _
-
"N:\Inspect\New Reject Tag Database\Reports" & _
-
"\" & strReportName & ".PDF"
-
-
Call SendAnEMail(olSendTo:=strEMail, _
-
olSubject:=strSubject, _
-
olEMailBody:=strBody, _
-
olAtchs:=strAtch, _
-
olDisplay:=True, _
-
SendAsHTML:=False)
-
-
aFile = _
-
"N:\Inspect\New Reject Tag Database\Reports" & _
-
"\" & strReportName & ".PDF"
-
If Len(Dir$(aFile)) > 0 Then _
-
Call Kill(PathName:=aFile)
-
End If
-
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!
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 :-)
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
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: - On Error GoTo EH
-
Dim olApp As Outlook.Application
-
Dim olMail As Outlook.MailItem
-
Dim strArray() As String
-
Dim intAtch As Integer
-
-
Set olApp = CreateObject("Outlook.Application")
-
Set olMail = olApp.CreateItem(olMailItem)
Says Compile Error: User-Defined type not defined
Whats Wrong here?
Nevermind, I'm an idiot...For got the reference Library part.
After the reference to Microsoft was added, the only part that hangs on compiling is the - Call SendAnEmail(olSendTo:=strEMail, _
-
olEMailBody:=strBody, _
-
olAtchs:=strAtch, _
-
olDisplay:=True, _
-
SendAsHTML:=True)
What is the error you are receiving? That's where we have to start.
NeoPa 32,556
Expert Mod 16PB
You should also post all the relevant code so we can see the context.
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. - Public Function SendAnEmail(olSendTo As String, _
-
olSubject As String, _
-
olEMailBody As String, _
-
olDisplay As Boolean, _
-
Optional olCCLine As String, _
-
Optional olBCCLine As String, _
-
Optional olOnBehalfOf As String, _
-
Optional olAtchs As String, _
-
Optional SendAsHTML As Boolean) As Boolean
-
On Error GoTo EH
-
Dim olApp As Outlook.Application
-
Dim olMail As Outlook.MailItem
-
Dim strArray() As String
-
Dim intAtch As Integer
-
-
Set olApp = CreateObject("Outlook.Application")
-
Set olMail = olApp.CreateItem(olMailItem)
-
With olMail
-
.To = olSendTo
-
.Subject = olSubject
-
-
If SendAsHTML Then
-
.BodyFormat = olFormatHTML
-
.HTMLBody = olEMailBody
-
Else
-
.Body = olEMailBody
-
End If
-
-
.CC = olCCLine
-
.BCC = olBCCLine
-
.SentOnBehalfOfName = olOnBehalfOf
-
strArray = Split(olAtchs, "%Atch")
-
-
For intAtch = 0 To UBound(strArray)
-
If FileExists(strArray(intAtch)) Then _
-
.Attachments.Add strArray(intAtch)
-
Next intAtch
-
-
If olDisplay Then
-
.Display
-
Else
-
.Send
-
End If
-
-
End With
-
Set olMail = Nothing
-
Set olApp = Nothing
-
-
SendAnEmail = True
-
-
Exit Function
-
EH:
-
MsgBox "There was an error generating the E-Mail!" & vbCrLf & vbCrLf & _
-
"Error: " & Err.Number & vbCrLf & _
-
"Description: " & Err.Description & vbCrLf & vbCrLf & _
-
"Please contact your Database Administrator.", vbCritical, "WARNING!"
-
SendAnEmail = False
-
Exit Function
-
End Function
-
-
-
Public Function FileExists(ByVal strFile As String, _
-
Optional bFindFolders As Boolean) _
-
As Boolean
-
'Purpose: Return True if the file exists, even if it is hidden.
-
'Arguments: strFile: File name to look for. Current directory searched if
-
' no path included.
-
' bFindFolders: If strFile is a folder, FileExists() returns False
-
' unless this argument is True.
-
'Note: Does not look inside subdirectories for the file.
-
'Author: Allen Browne. http://allenbrowne.com June, 2006.
-
Dim lngAttributes As Long
-
-
If Not (IsNull(strFile) Or strFile = "") Then
-
'Include read-only files, hidden files, system files.
-
lngAttributes = (vbReadOnly Or vbHidden Or vbSystem)
-
-
If bFindFolders Then
-
'Include folders as well.
-
lngAttributes = (lngAttributes Or vbDirectory)
-
Else
-
'Strip any trailing slash, so Dir does not look inside the folder.
-
Do While Right$(strFile, 1) = "\"
-
strFile = Left$(strFile, Len(strFile) - 1)
-
Loop
-
End If
-
'If Dir() returns something, the file exists.
-
On Error Resume Next
-
FileExists = (Len(Dir(strFile, lngAttributes)) > 0)
-
Else
-
FileExists = False
-
End If
-
-
End Function
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.
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 - Call SendAnEmail(olSendTo:=strEMail, _
-
olEMailBody:=strBody, _
-
olAtchs:=strAtch, _
-
olDisplay:=False, _
-
SendAsHTML:=True)
I forgot to add the subject line: see Line 79 from Post #5.
That'll do it.
That did it, it has completely compiled. Now I'll test the functionality of it.
Works perfectly...Thanks so much for the help and all the tips and tricks.
Great news! Let us know if there is anythings else!
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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"...
|
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...
|
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 ...
|
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...
|
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...
|
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.
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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...
|
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...
| | |