Email Attachment VB Script (1 Viewer)

[Cronk]

The problem is trying to attach an attachment that does not exist.

Test if there is an attachment in the loop.

For x = LBound(varPaths) To UBound(varPaths)
if len(varPaths(x) & "") >0 then
.Attachments.Add varPaths(x)
endif
Next

 

[Nancythomas]

I am trying to use the button for two things.
1) if there is an attachment in the data table (attachment) email the attachment and a report from the reports section for a particular record

And

2) when there is no attachment, then it should email only the report.

I hope I explained it well

 

[arnelgp]

please test, i already made the changes. the db is in the link below
https://www.dropbox.com/s/j6behdtioq892lg/Master EmailAttachment Datatype.accdb?dl=0

 

[GinaWhipp]

Will wait to hear what happens when you try arnelgp's suggestion...

 

[wasim]

Hi,

I have similar issue. I want to send the report and attched documents when i click the commond button. The attached documents are hyperlinked. How i can sue the above code for hyperlink documents to export to a folder any help? Or any sample DB?

Wasim

 

[arnelgp]

do you already have a form and a button for sending the email with attachment?
please give detail.

 

[wasim]

Yes. I have a form. The form is having the record. When i click mail command button, a report should be created based on the data available in the form. This report along with the attchments realted to this data should be mailed to the email address.
The following documents to be emailed to the person email available in the form:
1. PDF Report
2. All the attachment documnets related to the record from a query(The attachements are hyperlink and stored in different place. The link is avialable in the query. )

These two should be emailed together. How i can do this. I saw this tread. My problem is similar. But the documents are hyperlinked. which should be attached with the report.

Thanks.

Wasim

 

[arnelgp]

do you have current code for sending email?
for hyperlink fields, you can extract the Address portion from the field:

strFile = me.HyperLinkField.Address

 

[wasim]

Dear ArnelGP.

I attached db sample for your review and suggestion. Please check and advise me.

 

[arnelgp]

i dont know it this is a stripped version, but i encounter error while opening the Report. (photograph object is missing).

anyway try using other report just for a test.

 

[wasim]

Dear Arnelgp

Thanks for your suggesttion.

Reffering to below part in code :

column 7 is the attachment file
' add additional attachment file
If Trim(Me.lsthosp.Column(7, j) & "") <> "" Then
ReDim Preserve filenames(UBound(filenames) + 1)
filenames(UBound(filenames)) = Me.lsthosp.Column(7, j)
End If

I think as per the above it takes attachment files from list box of form. But I want to get the attachment files from Query field name: "ALink" which is as below:

SELECT * FROM EVALDETAILQ2 WHERE (EVALDETAILQ2.EVALID)= " & Me.EVALID.

Is it possible ? Any Help?

Wasim

 

[wasim]

Hi,

Any Help?

Wasim

 

[arnelgp]

your listbox rowsource has same sql statement with EvalDetailQ2.
if you will open your form, you will notice that ALink field is included in the listbox.

 

[wasim]

Thanks Arnelgp, Now i removed it from list box. I want to get the data from the query:
SELECT * FROM EVALDETAILQ2 WHERE (EVALDETAILQ2.EVALID)= " & Me.EVALID.

How i can do this?


Wasim

 

[arnelgp]

here is your db, check if there are errors.

 

[wasim]

Great!! Thank You very Much. It works perfect.

Wasim

 

[wasim]

Hi, Arnelgp, reffering to above attachment, if i have the path name to save the files on my form, how i can modify the code in the attached db? The file saving path field is on the form named as"Filepath", where i will enter the folder location where i want to save the file. This will help us togive mutiple users to save the files in their own choice of folder.

Please help me to sort out this.

 

[arnelgp]

just create a variable on your code, and set its value to the textbox on your form:

Dim strPath As String
...
...
strPath = Replace(Me.textbox_path & "\", "\\", "\")

replace all hardcoded path on your code with strPath, instead:

"C:\Users\Desktop\TEST1\Job Application Review Form.Pdf"

use:

strPath & "Jov Application Review Form.pdf"

 

[wasim]

Thanks. Perfect!!

 

[wasim]

Dear Arnelgp,

i here with attached sample db which is corrected by you. I want to update the table POSITIONAPPLIED, based on the form and list box data once mail is sent. But it updates all the line data. But i want to update only data related to the record available in form list box. Please look into this as suggest me the correct code.

Thank you for your hep.

The code is as below:

CurrentDb.Execute "UPDATE POSITIONAPPLIED SET POSITIONAPPLIED.PEVALID = " & Me.EVALID _
& " WHERE (((POSITIONAPPLIED.POSITIONAPPLIEDID)= " & Me.lsthosp.Column(0) & ") And ((POSITIONAPPLIED.APPLICATIONID)= " & Me.lsthosp.Column(3) & "));", dbFailOnError

Any help?

wasim