Sending emails from Excel attaching multiple attachments

Posted on 2014-09-30
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
Question by:martywal
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3

Author Comment

ID: 40354135
I don't think this uploaded earlier. Sorry

Author Comment

ID: 40354214
Also the emails are sent via Outlook
LVL 26

Expert Comment

ID: 40355503
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

LVL 26

Accepted Solution

Nick67 earned 500 total points
ID: 40355593
Sample attached.
Have a look at the code and button on the new worksheet

Author Closing Comment

ID: 40355831
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!!!
LVL 26

Expert Comment

ID: 40355874
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.


Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Viewers will learn the different options available in the Backstage view in Excel 2013.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Suggested Courses

630 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