-
Dec 14th, 2017, 04:35 AM
#1
Thread Starter
Hyperactive Member
[RESOLVED] Attach multiple reports to emails
Hi I have a code which I use to send out attachments with reports but now I need to add two attachments to the reports. So in column A will be report 1 name and column B will be report 2 name and in column C will be the email address. I would like help on how to change the script below to add report 2 as well.
The other thing I want to do is to have a message box for the file path as it could change each month. Thanks
Code:
Sub Email()
mypath = "C:\ Reports\"
Set oboutlook = CreateObject("outlook.application")
For Each cel In Range("a2:a500")
If IsEmpty(cel) Then Exit For ' exit on empty cell
Set nm = oboutlook.CreateItem(0) ' new mail item
With nm
.To = cel.Offset(, 2) 'email address col C
.Attachments.Add mypath & cel & ".xlsx"
.Body = “Please find attached reports. Thank You."
.Subject = "Reports"
.Save
.Send
End With
Next
End Sub
-
Dec 14th, 2017, 06:35 AM
#2
Re: Attach multiple reports to emails
try
Code:
Sub Email()
mypath = "C:\ Reports\"
Set oboutlook = CreateObject("outlook.application")
For Each cel In Range("a2:a500")
If IsEmpty(cel) Then Exit For ' exit on empty cell
Set nm = oboutlook.CreateItem(0) ' new mail item
With nm
.To = cel.Offset(, 2) 'email address col C
.Attachments.Add mypath & cel & ".xlsx"
.Attachments.Add mypath & cel.offset(, 1) & ".xlsx"
.Body = “Please find attached reports. Thank You."
.Subject = "Reports"
.Save
.Send
End With
Next
End Sub
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Dec 14th, 2017, 07:55 AM
#3
Thread Starter
Hyperactive Member
Re: Attach multiple reports to emails
That works perfectly. Is there an way to have a message box for the file? After c:\Reports\ there will be a folder which will change each month.
-
Dec 14th, 2017, 03:10 PM
#4
Re: Attach multiple reports to emails
Is there an way to have a message box for the file?
yes of course, an Inputbox! or you can have a file dialog or folder browser
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Dec 15th, 2017, 04:50 AM
#5
Thread Starter
Hyperactive Member
Re: Attach multiple reports to emails
How do I add a file dialog or folder browser? I'm ok with the inputbox.
I need to have the "C:\Reports\" bit hard coded and then the sub-folder as a user prompt.
Last edited by fusion001; Dec 15th, 2017 at 05:21 AM.
-
Dec 15th, 2017, 05:42 AM
#6
Re: Attach multiple reports to emails
you can use excels builtin dialogs, depending on what version you are using as to the code, for later versions, try like
Code:
with application.filedialog(mofiledialogfolderpicker)
.initialfilename = "c:\reports"
.show
myfolder = .selecteditems(1)
end with
there are some other options that can be set, but probably are not required for this application, i believe you should only use the initialfilename property one time
beware of any typos i may have made
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|