Solved

Sending emails from Excel attaching multiple attachments

Posted on 2014-09-30
6
114 Views
Last Modified: 2014-10-01
Hi Experts,

I have a little doozy that I'm trying to fix asap. Essentially I'm trying to send 2 emails from within a workbook. I've attached the admin page that shows what needs to be sent and to where.

The first email will go to Registrations and it needs to have up to 3 attachments (PDFcatch, PDFprocess & PDFreport = only if Result is Required) along with the text in the email body at the base of the page. It needs to be sent to the TO & CC address.

The second email is called Submission and it contains an excel file along with the text in the email body at the base of the page. It only needs to be sent to the TO address.

I've managed to create a few modules that send the workbook but I now want to attach the files that are referred to in the cells (named ranges).

I'd love some help...
I'm assuming it can be done.

Crossing fingers
0
Comment
Question by:martywal
  • 3
  • 3
6 Comments
 

Author Comment

by:martywal
Comment Utility
I don't think this uploaded earlier. Sorry
EmailForm.xlsx
0
 

Author Comment

by:martywal
Comment Utility
Also the emails are sent via Outlook
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
It can be done.
Of course you didn't attached an xlsm, xlsx have absolutely no code, so I cannot know how close you are.
Emailing code has some  prerequisites
I assume you have them all in place
(Open Outlook, the namespace, the folder, the explorer)
You then create a new message
Dim objOutlookMsg As Outlook.MailItem
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)


Attachments are dead simple
You need an attachment object
Dim objOutlookAttach As Outlook.Attachment

' Create the message.
Dim objOutlookMsg As Outlook.MailItem
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

'Create the attachment object
Dim objOutlookAttach As Outlook.Attachment
Set objOutlookAttach = .Attachments.Add(SomeCompleteAndValidFilePathAndFileName)

Open in new window


That's it
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
Comment Utility
Sample attached.
Have a look at the code and button on the new worksheet
EmailForm.xls
0
 

Author Closing Comment

by:martywal
Comment Utility
This worked a treat.
The code that I was running was all over the place so I stripped it out and started again with this solution and replaced the named ranges that I had.
Thanks heaps for the input!!!
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
If you are able and willing to push up a sample file of what you did in the end, that's good for others over the long run.
If you can't then you can't

Glad you got it figured out.

Nick67
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now