Jump to content


Photo

Send Each Pivot Sheet to a different email

pivot table email VBA

  • Please log in to reply
4 replies to this topic

#1 Carlton Seymour

Carlton Seymour

    Advanced Member

  • Members
  • PipPipPip
  • 41 posts

Posted 27 March 2014 - 01:03 PM

I have a Pivot table that I have filtered to create multiple pages as per expert lesson 5.11. I need to send these multiple sheets to multiple emails. Each email should only receive their sheet and not get another sheet or the workbook.

 

Appreciate your assistance with this

 

Regards

 

Carlton



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 27 March 2014 - 06:10 PM

Hi Carlton,

 

Assuming that you are using Microsoft Outlook as your e-mail program, you can do this by using one of Excel's 'hidden' commands.  The command is named "Send to Mail Recipient" and you'll find it in the Commands Not in the Ribbon list while customizing the Ribbon or Quick-Access Toolbar.

 

For instructions on adding 'hidden' commands to the Quick-Access Toolbar, see the video lesson: Expert Skills Lesson 4-16: Use Speak Cells to eliminate data entry errors.

 

The Send to Mail Recipient command offers you the ability to "Send the current sheet as the message body".  This will send only the currently selected sheet as part of the body text of an e-mail.

 

emailsend.png

 

This might be exactly what you're looking for, but I suspect that you'd prefer to send each sheet as a separate Excel Workbook.

 

Unfortunately, there isn't an easy way to split each worksheet into a separate xlsx file and attach them to e-mails.  If you want to do this, you will have to manually copy the worksheets into new Excel workbooks and then attach them individually. 

If this was something that you needed to do regularly, you might be able to speed up the process by recording a macro.

 

Recording macros is covered in the video lesson: Expert Skills Lesson 9-10: Understand macros and VBA.

 

If you need any more assistance, please feel free to reply.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Carlton Seymour

Carlton Seymour

    Advanced Member

  • Members
  • PipPipPip
  • 41 posts

Posted 28 March 2014 - 02:54 AM

Thanks Jonathan

 

This is part of what I want and I am OK to send only a sheet instead of the entire workbook as I have sensitive information in the workbook. My second and more important step is to send the 50 sheets created in the workbook to 50 different emails as each sheet relates to performance by that company. I need to automate this

 

Yes Outlook is being used here. Would it be different for Gmail

 

Regards

 

Carlton



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 28 March 2014 - 11:00 AM

Hi Carlton,

 

It might be possible to automate this process by recording a macro, but I suspect that it goes beyond the limitations of what you can do in 'standard' Excel.

 

To achieve the results you want automatically, you may have to resort to writing VBA code or using 3rd party tools.  Unfortunately, we can't currently offer support for these skills, although we are planning to release an Excel VBA course in the future.  For more on this, click here.

 

Please let me know if I can be of any more assistance.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 Carlton Seymour

Carlton Seymour

    Advanced Member

  • Members
  • PipPipPip
  • 41 posts

Posted 29 March 2014 - 02:39 AM

Thanks Jonathan

 

I do agree with you and felt this was beyond the scope of excel. I am presently studying VBA and will see if this will assist me. best wishes always

 

Carlton







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users