Email Attachment VB Script (1 Viewer)

Nancythomas · Aug 17, 2015

I am trying to send an email with the atttachment in the database as well as the Report as PDF. This works fine.

The problem is when there is no attachment, it will not email the report.

Where can I add (and / or) to the script.

1. The email button should email the attachment and the report.
2. If the attachment is not available, then it should only email the report.

Please help

arnelgp · Aug 17, 2015

you may test, i have not configured my outlook yet:

Nancythomas · Aug 17, 2015

Hi Thank you for helping me. I tried to run the script but I am getting error messages.

When the button is pressed, if there is no attachment, then the email should send only the Report as PDF. When email attachment and Report is available then it should send both (Attachment from DB and the Report as PDF).

arnelgp · Aug 18, 2015

ok i made some changes, please test it again.

Nancythomas · Aug 18, 2015

Thanks for helping me
When I run the db i am getting the below error

The expression may not result in the name of a event procedure

arnelgp · Aug 18, 2015

from which form and event are you getting the error.
can you do the debugging from there. open your AVLOG_Frm from in design view.
go to the Click event code of your "Email Attachment" button. on the first line of sub press F9 (breakpoint), then go back and run your form as normal. click the "Email Attachment" button, it will put you in debug mode. press F8 key to step through the code until you find which part of the code has bug.

by the way im not getting any errors on my pc.

Nancythomas · Aug 18, 2015

Hi

Thanks it worked (it only works when the attachment is available), but if there is no attachment, I does not email the report.

I would like to email the report if there is no attachment.

Please help

As stated earlier the email button should email the attach and the report but if no attachment is available, then it should only email the report.

I hope you understand what I am trying to explain.

Gasman · Aug 18, 2015

I cannot open accdb files, but I'd have thought you would be testing to see if an attachment exists?, if not, do not process the attachment part of the code.?

Nancythomas · Aug 18, 2015

The email button does the below operations
1. it takes the attachment from the data field (attachment) and moves it to a FOLDER that gets emailed.
2. It also takes the report from the report list and moves it to a folder that also gets emailed.

The problem with this script is when there is no attachment (1), it does not send the report (2).

I need to insert a script IF NOT (Item 1) then process (Item 2) as part of the code

If there is not attachment (1) then email the Report (2).

I hope this is clear.

Thanks

arnelgp · Aug 19, 2015

hello ms. thomas, try this again.

Gasman · Aug 19, 2015

I'll leave you in Arnelgp's capable hands, but personally I always like to understand the code.

If this was my project I'd be copying the relevant files to that folder and then attach all the files in the folder, which should always be at least 1 ((the report) or in your case 2 if the attachment field is populated. Regardless I would code it so that it would work from 0 to as many files as exist, so that I would not have to go back and maintain that code if the requirements changed.?

But as my signature says I am a newbie at Access, so there might be better ways.

Good luck with it anyway.

Nancythomas · Aug 20, 2015

Thank you very much for helping me.

When I run this I get an error message as below:

Runtime error-2147024894
Cannot find the file. Verify the path and file name are correct

.Subject = "Availability Lot for " & Me.AVLOGID & " At " & Me.[Date]
.HTMLBody = "Some text here"

' Add attachments
For x = LBound(varPaths) To UBound(varPaths)
- .Attachments.Add varPaths(x)
Next

.Display
End With

The application also gives an error Code 2059
Cannot find the object '!1'.

Gasman · Aug 20, 2015

You need to walk through the code with F8 and hover over the variables or put them in the Watches window and see what they contain.

I'd be looking to see what varPaths holds. When you add an attachment (you must supply the FULL pathname like "C:\temp\reports\report1.pdf" and not just "report.pdf", so I am hazarding a guess you do not have the folder path to the file?

Nancythomas · Aug 20, 2015

Can you please explain where do I add the full pard of the folder in my script. Please help..
Please open my db script and tell me where to add the folder name...

Gasman · Aug 20, 2015

I cannot open accdb files I only have 2003.

Before the code you posted I would have something like

Code:

Dim strFullPath as string
strFullPath = "c:\temp\reports\"

and then where you add the attachment

Code:

.Attachments.Add strFullPath & varPaths(x)

change the strFullPath value to your location and make sure you have a correct file name in varPaths(x) that includes the extension otherwise you will need to add that as well.

HTH

arnelgp · Aug 23, 2015

sorry for the delay i have no internet for the past days, here try it again.

rbh1090 · Aug 24, 2015

Hi, check this out: http://www.msaccessdeveloper.pro/#!Send-emails/c6x8v/55b1b20c0cf22effe2e464ec

Nancythomas · Aug 26, 2015

Sorry I tried all but it does not work.

GinaWhipp · Aug 26, 2015

How did you test this? I see no real eMail addresses or path information? Or maybe I'm confused as to which Form and what code you are using. That said...

If you are just looking to only add when there is an attachment, try...

Code:

Dim strAttachments As String
 
strAttachments = "Your Path Goes Here"
 
If strAttachments <> "" Then
     .Attachments.Add strAttachments
End If

arnelgp · Aug 26, 2015

i test if there is no attachment in attachment field, now when there is no record in subform.
https://www.dropbox.com/s/j6behdtioq892lg/Master EmailAttachment Datatype.accdb?dl=0

Email Attachment VB Script (1 Viewer)

Registered User.

Attachments

..forever waiting... waiting for jellybean!

Attachments

Registered User.

..forever waiting... waiting for jellybean!

Attachments

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Enthusiastic Amateur

Registered User.

..forever waiting... waiting for jellybean!

Attachments

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

..forever waiting... waiting for jellybean!

Attachments

Registered User.

Registered User.

AWF VIP

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread