Bulk Emails | Word template as Outlook Body | Excel macro(vba)
HTML-код
- Опубликовано: 28 май 2024
- Learn how to send bulk emails using excel vba, outlook and use body of message from Word document. In this example, we are sending performance letters to employees. We have template of email in word document and we are generating personalized email body using excel macro.
You can ask your questions in comments section.
Subscribe and Like to keep me motivated so that I produce more and more content for you!
It is brilliant!! Thank you!
Glad you like it!
Thanks Very much for the informative valuable illustration.
It helped me a lot.
An excellent tutorial, thanks for posting this.
Really Your super Hero of VBA 🙏🙏😎😎🔥🔥
So nice of you
Very useful. Thanks for sharing. Keep going!
Thanks mate..Keep sharing and stay connected !
It helped a lot. Thank you very much :)
Thanks for sharing your knowledge..its great help!!
Stay connected!
Very well explained, thank you 👌
Glad it was helpful!:-)
It is awesome. Working fine.Thanks
Life / work saver!!!! 👏🏼
Thank you for the video :)
Wonder how to include different attachments for different emails using this code?
Excellent tutorial, quick question... what if I need to have a field empty in the Word document ? what code should I use in VBA
Great explanations!!!
Thanks!
Great video! Is there a way of applying only to filtered rows?
Thank you so much for your kind help.
Sure. Please share this content in your network👍
You have explained it brilliantly! I have one query on this: In my case I am putting a very large table in the mail body and during macro execution Outlook is getting hanged and thus macro stops working. My code is working fine when I put a small table or text. Please suggest me how I can resolve this issue. Thanks.
A big thank u ❤
Thanks. I have been looking for this long time. Could you please also explain on how to find and replace images in Word editor similarly. Like, if we want to include image of the employee as well. Given that images of all employees are saved in a folder as JPGs with filename = employee name or ID?
It is Awesome, I love it.
Sir is there a chance we can attach/embed the word file in excel itself and use it as one file?
Nice Video! Very useful.
How to put clickable hyperlinks in one of the Value that changes as other Values change as well?
Could you please advise is it possible to add different word document to user as specific criteria
Thanks for the explanation. By exactly following your steps, I have developed a tool that can automate.
But I found it challenging that if I want add a link to my email where the hyperlink is stored in worksheet. range (1, 6), it only pasts a text (the hyperlink text) in the email body? Please assist how can copy this link to the word doc and then pasted to my email.
Thanks for the video its helped.. i have tested two cases. however, first row data i mean employee A data is capturing for employee B.
Also, is there any way i can add the table if single employee having more line items ?
Hi Kamal, could you post your code in the secription or a publicly accessible google doc?
Gr8, would be very helpful for learner if attach practice workbook
I suggest you to follow this playlist. I have shared link to github repo with all code snippets and documentation. Excel VBA - Outlook Automation | Bulk EMAILS: ruclips.net/p/PL1R_HJw0CDYKtGsYLUXyC_qvEgKZ7kb2N
Thanks, great video. Is there any way to send the emails without having to display each one first?
is there a way to to send it only to visible rows? I want to be able to filter rows and then send the email?
Great!
I am thinking that this will work for a Word document that is not a template that is sent to several emails with individualized attachments?
Can we send all employee performance data of the email body in the form of a picture with its name and performance number
Hi.
Could you please advise how to send internal email only.
Actually, I want to automate bulk email in my organization outlook that microsoft Azur classification.
Could you please advise how to automate this before send email
Hi, I'm getting some errors. I'm really new to VBA
1. Variable r not defined, how to define that.
2. I need to add few attachment to the code
Thanks. Please help me in how to find and replace words within Textbook in word.
Kamal, This is working absolutely fine; however, I want to send from my shared mailbox and upon using .sentOnBehalfOfName It's not working. Could you please make a video on this please
Dear Kamal,
Thank you for your clear and simple video. It helps me a lot in my work !
Just one question :
In my Word template I have a blank line inserted between each line :
Line 1
Line 2
Line 3
But in the mail, there is no more blank lines :
Line 1
Line 2
Line 3
Could you tell me why ?
Hi. It's very good. But pls confirm if it is same as mail merge.
Some of the text I want to copy over exceeds the 255 character limit, any suggestion ideas ?
Hi! Thank you for this video. It was really helpful. I have a question here. How to avoid the outlook security alert message?
In outlook settings you need to enable programming access..
Extreme Automation - Kamal Girdher. Is there is any other options to do this thru vba since I don’t have this access in my company to change the outlook alert settings?
No other way.
How would you have it open a specific word template for each row?
This is great information.
Where can I find a copy of this code?
Please provide link to download.
i guess it is kinda off topic but do anybody know a good website to stream new series online ?
@Harvey Amari Thanks, signed up and it seems to work =) I really appreciate it!!
@Jesus Prince Happy to help =)
Hello Ad! It's really helpful! But I could not see the "Getlnspector.WordEditor" in my computer. Where could I get it? Do I need to download or addin? Thank you so much!
I did again. It's not bad. But there is the notice 'Do you want to save the last copy of ....If yes it might take a bit longer to close. How to stop this pls?
This was so helpful. Can you show how the email outlook signature be applied as well. Signature does not show.
I figured it out. So simple all I had to do is copy my outlook signature & paste it to the word document.
glad you figured it out.
Great video. The macro works fine on the first loop but on the second it appears an error message in < Set doc = Documents.Open(Range("B2").Value)">. Do you know why this might happen and how to fix it?. Thank you very much.
Can you share your file please?
How to assign special tabel i.e different different tables in the email draft for different different recipients.
i come across with many reference on the internet but yet cant find one appropriate....wanna see if you could help.....my situation is:
i want to have a dynamic subject for my mail merge, in which i've obtained which work for me already....but i cant find one that i could cc some one.
I'm using the mail merge function in word with data in excel, what i'm looking for is:
when i complete the mail merge, the each email will be sent e.g. "TO: email_A; CC: email_B,C...."
is that possible to write a vba code to make it happen in word?
Or, in alternative, by following yours, possible to run a dynamic subject like "SUBJECT LINE - No.XXX" where XXX is the string i want to be different in each mail.....
Hi, I am having error of variable not defined for "For r = 5 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row", can please advise?
same!! please help
Hi Kamal i want to buy the Macro but adding delay function how much you will charge me?
I tried running the code but I got "Error 5097" which points here: Set Editor = .GetInspector.WordEditor
Editor.Content.Paste
what could be the problem? I'm new to VBA please help
Yes, this code was working fine but from couple of days I am also getting same error.
Kamal Sir- your support pl.
Where can I find the code? This looks great but I cannot locate the code. Thanks.
Can u make this without opening the word doc, meaning, work in the doc editor inside the email body?
I'm getting only one Mail Item while Running this ! and the"Doc.Content.Copy" Code is not working Could you help me to Fix it
This is very helpful. Thanks a lot
But I m getting a dialog box saying "do you want to keep the last item you copied"
I want to send it to 100 ppl hence 100 times the dialog box is occurring
Can we give some command in VBA for this dialog box ??
If you see an alert in excel then use
Application.DisplayAlerts=False
Before you copy
And
Application.DisplayAlerts =True
after you paste or close the file.
Hi Kamal, I am new subscriber and I found this video is very useful. Related to this query actually many Word file is opening with this comment. Can you please provide the resolve code. Thank you.
Hi.
I am one of the guys which is having the error runtime 5097, Word has encountered a problem. After several checks I understand the reason of this error are some Windows updates. While trying to debug, I am pointed to Editor.Content.Paste . Is there an alternative in the code to avoid this error ? It was working perfectly until the Windows Updates. Maybe some registries are affected because of this.
Please send me the file and share details of where exactly you are facing the issue. I do not have these files handy with me. It would be easy for me to look into the issue if you can share the file exactly like where you are facing the issue.
Sir it's was easy for your all subscriber nd viewer if you will provide code link or xlm link, than you, waiting for ur answer
Please provide us file link, thankyou sir
if we have three different data send email to one person... instead of sending 3 emails i want to send one email with all three data to same in excel format
Sir, Thank you so much, but here 1 error shows (error object variable or with block variable not set) is it shows due to we did not define workbook?
Plz answer
send your file to me please.
Sir, if I wants to use same word document and not dynamic like your explanation at which step I can stop coding as per your script
In that cases you should not use word template. You should draft the email in outlook and save it as outlook template. Then while creating new item you should use CreateItemFromTemplate instead of CreateItem function.
Hi, how to use word template as email reply based on subject line?
Sir
Please create VBA program in outlook developer also.
Sure. Thanks for the suggestion.
in that email body, how to add hyperlink word doc. for specific line or end of line
Do u have any VBA web Scrapping tutorial ?
Yes. www.udemy.com/course/webscrapingusingvba/?referralCode=827ADA0FE23151D7794D
.getinspector.wordeditor is throwing an error stating "operation failed".
Working fine for display not for sending.
What if the data has links also how to add that to template
Hello Kamal,
Thanks for this video
I have tried to make tool with your coding and it works perfectly
But my word application is not closing . Let say if i have 5 names then it is showing five word application open
Please let me know if you have solution for this as well
Thanks Again
can you send me your file please?
Same issue for me as well
is this issue resolved?
any other code for attaching the attachment with word template
Sir my word file in open to copy the content but its not close automatically. It shows copy to clipboard yes or no. Why this happen and how to solve this
please tell
Sir, I'm getting as variable not defined for Editor and content. Can u plz help me to know the error.
Set editor=.getinspector.wordeditor
Editor.content.paste
Hi, did you ever find a solution for this? My macro fails and debug takes me to 'Editor.Content.Paste'.
@@bobbyyorba2610 Did you find a solution. I'm stuck as well.
How can i send it from my group email id. I don't want it to send it on my personal id
I created using same and while I am displaying it and it is working perfectly but when I am trying to send then it's sending but blank email with correct subject..
@Mandeep Puri same issue im facing. Please help to solve
Please send your file to me.
@@xtremeExcel mail has been sent to excel.microk@gmail.com please help to solve this issue
Hi Sir, this coding is very helpful for me but I am facing issue in getinspector section
All things ok but nothing shows in mail body .. please hlp sir...🙏🏼
Hi Neeraj, Please share your file so that I can check.
Any update on the getinspector error?. I'm badly stuck due to that issue.
for some reason when trying to open the word doc it doesn't open, it only opens the word app but then it stays gray with no document opening, do you know why?
can you please share that workbook with us.
Get Error
Editor Variable not define
Set Editor = .GetInspector.WordEditor
Editor.Content.Paste
How can define and resolve this error.
this is happening for me also. sir, can you please give the solution.
if we have table and graph than how can we add
I am getting error for GetInspector method I have added all the add-in references but unable to run the code
How to replace text in this word document by a hyperlink (excel range) to a website or mailto-address with vba?
what if i want to connect hyperlink in cell to a name in word document. sir can you please help me.
name in word document means?
@@xtremeExcel I think I have the same problem.
Firstly I appreciate your explanation on the subject. By exactly following your steps, I have developed a tool that can automate.
But I found it challenging that if I want add a link to my email where the hyperlink is stored in worksheet. range (1, 6), it only pasts a text (the hyperlink text) in the email body? Please assist how can copy this link to the word doc and then pasted to my email.
Dear Kamal Sir,
would like to bring in your notice that the code is displaying all the emails but not sending the emails.
Request you to please do check and rectify the problem occurred.
Instead of .Display if you use .Send, it will send the emails
Dear Sir,
Thank you very much for your reply.
If I use .Send, then the outlook is sending the email but not pasting the the content of Word template in the mail body of outlook.
If I use .Display, then the outlook is pasting the content of Word template in the mail body of the outlook.
Exactly this the problem.
Use.Display first, then paste the content, then use .Send
Getting error
For r = 5 to select. Cells(rows.count,1).end(xlup).row
getting run time error 424
object required
Close. Savechanges= false is not working...
Need a vba code for editing subject line as well.
very easy. just put .subject = "" in the with block.
Error occurred invalid
=. GetInspector.WordEditor
signature alignment is not correct.though nice video thanks
Is there anyway that we can attach pdf also
yeah. we can attach any file type.
And also I have found something challenging i am facing difficulty when I need to paste a word document with formatting as it as mail body
for that you need to use htmlbody instead of body
.send is not working but the display works
What error are you getting?
Few lines giving me error:
Set olm = ol.CreateItem(olMailItem)
Set wd = New Word.Application
but instead if i am changing it to
Set oa = CreateObject("Outlook.Application")
Set olm = oa.CreateItem(0)
Set wd = CreateObject("Word.Application"). it is opening word, but not giving the result,
Also i am getting error for below lines
Set editor = .GetInspector.WordEditor
WordEditor.Content.Paste
Please help
Hello Anil. Please send your file(having code) so that I can debug and tell you the issue.
@@xtremeExcel Have rectified all the errors. Now getting error on Set editor = .GetInspector.WordEditor and i am using office 16. is it because of some compatible issues?
I'm Getting the Same issue Here, and i Troubleshoot that Compatibility Issue again i'm getting the same Error
I'm also experiencing an error with "Set Editor =". Have you guys identified the challenge?
@@Malape1997 declare the editor variable it will work perfectly !
How to add CC?
In the part when adding the email subject and topic, add one line that says:
.CC = "TheNameOfYourWorkSheet".Cells(r, "Column#WhereCCEmailsAre".value
Replace the information within quotation marks with your information.
Hello bro
would you please send code ?
Sorry I do not have the file handy with me. In case you are facing challenge in the code explained in the video, please send that file to me. And I'll check why it is not working for you.
@@xtremeExcel I'd like to add attached in the mail
would you please help me ?
@@xtremeExcel Dear i want to remove Display
and send email Automatic and add attachment
T
Object variable or with block variable not set ... Run time error 91
In VBA script. With wd.selection.find
Please advise to proper run
Hey! did you ever find a solution to this? Getting the same error.
Fixed 2 issues in my code: 1.) was using .doc in filepath when my file was.docx 2.) Declared Editor as Word.Document
Compile error :Wrong number or arguments not properly assigned
please send your file to me. Details are on channel homepage.
not working :(🤔
what's the issue?
@xtremeExcel
Hi , it gave error
Run-time error '5174'
Application-defined or object-defined error
Using MS Office 2019
Hi Kamal,
Firstly, excellent solution. It has helped me a lot! I do have one question, though: one of my replacments are dollars and I would like to have this formatting. The template already has this coded, I have it as: {$«Amount_to_Factor» \# ,0.00} but when I run the wd.selection.find and .replacement.text it is giving me the number without the formatting. I tried to add the number format at the end like this: .replacement.text=activesheet.cells(r,27).value.NumberFormat = "_($*#,##0.00_);_($*(#,##0.00);_($*""_""??_);_(@_)" but is giving me an error. Any ideas?
Again, thanks a lot for the knowledge sharing. It has been very enlightening
You've probably found a solution by now, but if anyone else has the same is try using .Replacement.Text = Format(ActiveSheet.Cells(r, 27).Value, "$#,###.00")
How to attachment the Word or pdf document with the same coding