Send a filtered table in the body of an email

[@BarbHendersonconsulting]

Thank you so much for watching my video. If you have any suggestions for future topics please leave them in the comments

[@99stranger99]

Hello Barb, thank you for this video, it is really helpful! Could you please make a video on how to send a mail automatically without opening the excel file with reminder of outstanding tasks to the team? Let's say the 2 lines in your mail in this video we're tasks not yet done and closed, and the first day of the month a mail would be sent to remind the team to do something about it. Is it possible?

[@BarbHendersonconsulting]

I have done something similar? kzbin.info/www/bejne/n6ipdHdnq9Vnjsk

[@Rinakor87]

Hi Barb, thank you so much for the video. Is there a way to re-use the same code on a duplicate Sheet in the same workbook? I've noticed we are referring to Sheet1. Say I have a workbook with multiple sheets and a new sheet is being added weekly, so I duplicate the latest sheet and rename it with the date of the updates manually and then send the table out. How can I edit the vba code, so that when I duplicate the Sheet with the vba code (if I have a button for example "send email") I won't need to make any changes to the code and email the updated table? Thank you!!!

[@BarbHendersonconsulting]

@@Rinakor87 instead of using sheet1 you could use Activesheet.

[@Rinakor87]

@@BarbHendersonconsulting thank you so much!!!!

[@semirhatic3914]

Thank you For putting this out there. I have tried over 30 codes not seem to work and finally come across yours and it is magnificent. Exactly what i was looking for. You are the best keep up the good work

[@BarbHendersonconsulting]

Great to hear!

[@Rajjan1960]

Thank you so much, this helped me tremendously! Sending information to 200+ loco drivers regarding their energy consumption last week can now be done in

[@BarbHendersonconsulting]

Excellent!

[@ajayardeshana5639]

Thank you so much, this helped me tremendously! I can use this to send email regarding my daily work updates to my team lead. Thanks for providing this useful information.

[@BarbHendersonconsulting]

You're so welcome!

[@manodurai1252]

Exactly what I was looking for. This saves me hours!!

[@BarbHendersonconsulting]

Happy to help!

[@masterpiwa]

Hello Barb Thanks for the amazing explanation. Question: How would the code look if we were to send the email to addresses which are part of the table (instead of the same email all the time) along with attachments. So, let's say that we add an "email" and attachment" column. Then we may have the same email address showing on the table more than one time (multiple entries/rows per email). The idea is that we send the email by filtering the "email" column. If we have 3 rows containing the same email, it would create an email, add 1 email address from the table to the email, include a snapshot of the table and add an attachment based on the number of rows displayed (1 attachment per row). Your help is greatly appreciated.

[@masterpiwa]

Just to provide some more context... My spreadsheet contains multiple recipients. Often times a recipient may be listed more than once. Currently, looking at some of your videos I know it is possible to make the macro run line by line where each row that meets certain parameters gets an email to the linked recipient. However, If I have one recipient who is listed multiple times, this person will get more than one email. The idea is that If a recipient is listed more than once, each row would be combined and added to the body of the email, each listed attachment per row associated to the same recipient added as attachments, and only 1 email is generated. Is it possible for my recipients who are listed more than once to get only one email rather than multiple email with 1 attachment per row?

[@BarbHendersonconsulting]

The easiest way to handle this would be to set up a list of emails and the account numbers on another page and filter the table by the account number and send to the email that is associated with the account number.

[@masterpiwa]

​@@BarbHendersonconsulting Thank you for your response. Sorry to be a bug but got one last question . I have the following code when it comes to adding attachments, but on line 3 I would like the attachment to be added to the Outlook email based on cell range from the filtered table. Line #1 = .attachments.Add "Enter Full path here for fixed attachment #1" Line #2 = .attachments.Add "Enter Full path here for fixed attachment #2" Line #3 = .attachments.Add "C:\RemoteAMBA\bin\SoftTokens\" & Range("g2").Value ' THE IDEA HERE IS TO HAVE THE CODE READ THE PATH DISPLAYED FOR EACH FILTERED COLUMN ("G" column) WHERE WE ARE FILTERING BASED ON EMAIL This is same thing that his happening when you filter the table and only add the filtered table to the body of the email, but this time, I would like the code to read filtered attachment as well. The above line only places the attachment listed on G2 cell. Are you able to re-arrange that line of code, please?

[@anagallardo6664]

Barb Henderson you are the best!! :) Thank you so much

[@BarbHendersonconsulting]

You are so welcome!

[@dadabhoyrus]

Fantastic video, straight to the point. Liked & Subscribed!

[@BarbHendersonconsulting]

thank you

[@Interestingboy642]

Love your videos very short and amazing information.

[@BarbHendersonconsulting]

Glad you like them!

[@danielbarton1694]

Thank you for taking the time to share your knowledge and experience, these are very helpful videos. I used your code to great effect but could only get the macro to work by adding in the Word library and commenting out the text AndFormat (wdFormat PlainText). Thanks again.

[@BarbHendersonconsulting]

Glad to help!

[@mohd68929]

Thanks for sharing I have one question if I run the macro it will send email but some time not send or delayed any advise

[@BarbHendersonconsulting]

If they send once, they should always sent, maybe something with the computer?

[@KusogeMan]

amazing lesson, you're a great teacher!

[@BarbHendersonconsulting]

Thank you! 😃

[@maneetchawla7318]

Hey Amazing video ! I was trying to find a similar work around but I have a list of members who I need to email the table at one go and that list is dynamic. Is there a way to work it around in the same code that you did?

[@BarbHendersonconsulting]

You could create a list similar to the one in this video kzbin.info/www/bejne/ZpybpWV8rpuhicU and send them that way?

[@jaick1015]

Hey, thanks for this helpfull Video! I have 2 more Questions: 1. Is it possible to adjust the size of the table? And 2. Can u sign the Email under the copied table? Would love to hear you back! Greetings

[@BarbHendersonconsulting]

I do not know if you can adjust the size of the table but the best way to send it would be using an outlook template kzbin.info/www/bejne/f5Oreohon96ebLM

[@yuhukuy8188]

Please help, error "object required" ? In my table there is a vlookup formula, is that what makes this code not run ?

[@BarbHendersonconsulting]

do you have all of these defined? Dim outlook As Object Dim newEmail As Object Dim xInspect As Object Dim pageEditor As Object Set outlook = CreateObject("Outlook.Application") it maybe what it is look for!

[@joeydiepstraten7001]

Hi, i have copied the macro from the video. However I get run-time error ‘438’ on coding :’PageEditor.application.start = len(.body)’ . How can i solve this?

[@BarbHendersonconsulting]

there is a lot of reason why you could be getting this error. Do a search on this error. There are all kinds of answers.

[@kishores4848]

Hi Barb, Thank you so much for this wonderful tutorial, can you please help me on how to add some text below the table, Example I want to add “please let me know if you have any questions, Regards xxx below my table how can I do that, Thanks in advance

[@BarbHendersonconsulting]

check out this video kzbin.info/www/bejne/mHXKkIJ_htFoaJY

[@kishores4848]

@@BarbHendersonconsulting Thank you so much for your recommendation , this was exactly what I was looking for :)

[@martu33127]

Thanks A LOT Barb, This was almost exactly what I was looking for. The only exception is that for each filter I apply I want to change the destination email. Can I also change this variable depending on the selected rows? Thanks a lot again for your tutorial!

[@BarbHendersonconsulting]

You would have to use a lot of if statements to link the data to email address, but it could be done

[@gergoszabo7080]

Very helpful video, is there a way to insert the selected range before the regardings? Thank you for your help in advance!

[@BarbHendersonconsulting]

Not that I know of

[@jillpeet2747]

Hello Barb! Great video & very informative! One of the columns in my table use icon sets as conditional formatting (green=1, yellow=-1, white=-2, and gray=0). It is also set to icon only. However when I run this Macro, the conditional formatting doesn't stay. In the email, it changes back to the number (saying only 1 or only -2). How can I fix this? Thank you so much for your time and tutuorial!

[@BarbHendersonconsulting]

When you copy the table it takes the formatting. I guess that it does not take icon sets!

[@Ilove_bini-d1z]

Hi, can i exchange the A1 in .to = " " into multiple emails instead? what the separator of is it ; or : ?

[@BarbHendersonconsulting]

You can list numerous emails in "A1" separated by a " ; "

[@makubexho]

Hi Barb, brilliant video and codes! I have a small puzzle here that I need to add a break line right after the table in the email body so I can continue to write or add stuff. With those codes in this case, how do I achieve? Thanks

[@BarbHendersonconsulting]

This is something that took me a long time to figure out but if you see this video kzbin.info/www/bejne/mHXKkIJ_htFoaJY I finally got it!

[@shankarraman9009]

Good explanation, I want to copy two different ranges say (A1:A46) from there visible columns till 46th row...is there any possibility... to bring to the Outlook... Great if you can help

[@BarbHendersonconsulting]

What I would do is copy both ranges to a temporary worksheet and then copy the complete sheet to outlook.

[@damienhettiarachchi9469]

Hi Barb, Thank you very much. This code works well for me. I also need to add signature as image or default signature after the table

[@damienhettiarachchi9469]

Please reply how to add

[@BarbHendersonconsulting]

the easiest way is to use a template kzbin.info/www/bejne/mHXKkIJ_htFoaJY

[@damienhettiarachchi9469]

@@BarbHendersonconsulting Thank You Very much & It Works for my task.

[@BarbHendersonconsulting]

@@damienhettiarachchi9469 your welcome

[@ruibrito524]

What are the active references in the vba for making the table in Outlook. The data comes through, but it is not in table format. thanks

[@BarbHendersonconsulting]

I am not sure

[@nervousfrog101]

Not sure if you are having the same problem as me but try changing pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText) to pageEditor.Application.Selection.PasteAndFormat (wdFormatRichText) Oh and @Barb Henderson thx for the script it really helped.

[@crafty_tams]

Thank you! That’s amazing!!

[@BarbHendersonconsulting]

Glad you like it!

[@conyliporace6505]

Hi Barb, I would like to send the filtered table to a complete list of supervisor (each supervisor should see their employees) what would you suggest to change in the code? Thanks in advance! 🙂

[@BarbHendersonconsulting]

All you would have to do is create a list of emails and loop through them

[@conyliporace6505]

Thank you! You mean creating another table on a different tab with all the e-mails? I had it in the same table..I am also getting an error here: Set xInspect = newEmail.GetInspector Set pageEditor = xInspect.WordEditor Hope you can help me again! 🙂

[@yup9889]

Im running to this problem but needing to send filtered data from 7 different sheets on excel, each sheet is a different custom. Can that be possible? Thank you

[@BarbHendersonconsulting]

This would be quite complex you would have to name each filtered table and attach them separately

[@yup9889]

@@BarbHendersonconsulting I see, I can probably record mute macro separately and consolidate it to a command button.

[@vidarpi]

I got an error saying "wdFormatPlainText" was undefined

[@BarbHendersonconsulting]

check that on Tools - References that you have the "Microsoft Office 16.0 Object Library " Checked

[@rajeshmajumdar4999]

Thanks Barb!

[@BarbHendersonconsulting]

You are so welcome!

[@0305SEA]

need to send selected range cell not table can work?

[@BarbHendersonconsulting]

Yes, sheet1.range("B2:F4).copy

[@rabinshrestha9335]

Hello Barb, I found your video very useful for professionals, who have maintained their data in excel and communicate same through mail. It would be much covenient, If we can customize and send single mail to recipient in "To" and "Cc" as per the data in the table of excel (same recipients having data in more than one row) with default signature of outlook.

[@BarbHendersonconsulting]

Everything you mention in your comment is possible. The default signature ranges from very tricky to impossible to achieve.

[@rabinshrestha9335]

@@BarbHendersonconsulting Can you please, help me with providing VBA code to customized table data as per reciepient in "To" & "Cc" section?

[@jeganr3312]

I have tried this, but the table formatting is not pasting properly in body of the email. one more thing i have a 1000s of data in excel in which multiple emails have to send based on multiple email ids. Please help me with my queries.

[@BarbHendersonconsulting]

I have email you

[@danielasevdin7976]

I also need help doing something similar, could you please provide some guidance on how to adjust so that it can send out multiple emails but only attach the data to that individual Email?

[@anabellazuniga3114]

Hello, why do you do a .display before you start to copy the table?

[@BarbHendersonconsulting]

It is a weird thing, if you do not display it first it will not copy the table

[@prathameshsawaratkar6771]

Hi Barb Henderson, This is very much helpful for me. please help me with below question - Here only one mail ID was there in .TO= So reference was given as "A1". what if i have more mail ID. Like from cell number A1 to A50 i have 50 mail ID to whom i want to sent mail. So how we will write command With newEmail .To = Sheet1.Range("A1").Text ???

[@BarbHendersonconsulting]

x=2 do while sheet1.cells(x,1) "" edress =sheet1.cellw(x,1).text "rest of the code" x=x+1 Loop

[@hassnainabid3193]

Thank you always :)

[@BarbHendersonconsulting]

Always welcome

[@ankiitupadhyaay5340]

I got error 1004 method range of object worksheet failed

[@BarbHendersonconsulting]

I do not believe that you have specified the correct table

[@laxmikulal6155]

Thankyou for the video. But what if i want to send email to 100 employee ??

[@BarbHendersonconsulting]

You should make a do while loop with a list of emails so that it will send to each email until there are none left

[@naderthermostate791]

Im getting an error The object doesnt support

[@naderthermostate791]

Error number 438

[@BarbHendersonconsulting]

Check your sheet names?

[@ayushbhatnagar716]

How to do it in app script

[@BarbHendersonconsulting]

I am not sure my expertise is VBA

[@Dakshmahir]

I want to do this on gmail. Plz guide

[@BarbHendersonconsulting]

I do not know how to do this on gmail.

[@Dakshmahir]

@@BarbHendersonconsulting thanks mam, I am working person i want to learn VBA, kindly suggest, from where i start

[@BarbHendersonconsulting]

@@Dakshmahir Sorry, I no long run online VBA courses

[@Dakshmahir]

@@BarbHendersonconsulting ok thanx

[@johnson.k.george1265]

Thanks a lot for this wonderful video.. am getting error 4198 in the command pageeditor.Application.seletio .PasteAndFormat(wdFormatPlainText)

[@BarbHendersonconsulting]

I looked it up : Error 4198 Crash - Error 4198 is a well-known, which happens when an incorrect line of code compiles into the source code of the program. This typically occurs when Microsoft Word cannot recognize that it is given an incorrect input, or is unaware of what it is supposed to produce.