Solved

Sending emails from Excel attaching multiple attachments

Posted on 2014-09-30
6
131 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
[X]
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
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 26

Accepted Solution

by:
Nick67 earned 500 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This collection of functions covers all the normal rounding methods of just about any numeric value.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

730 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