Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Sending emails from Excel attaching multiple attachments

Posted on 2014-09-30
6
Medium Priority
?
146 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
ID: 40354135
I don't think this uploaded earlier. Sorry
EmailForm.xlsx
0
 

Author Comment

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

Expert Comment

by:Nick67
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
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 26

Accepted Solution

by:
Nick67 earned 2000 total points
ID: 40355593
Sample attached.
Have a look at the code and button on the new worksheet
EmailForm.xls
0
 

Author Closing Comment

by:martywal
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!!!
0
 
LVL 26

Expert Comment

by:Nick67
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.

Nick67
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
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…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

877 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