Welcome back Andrew. As always, thank you for sharing your knowledge. You walked us through the procedure, showed us some of the problems we could encounter and gave us nice and quick solutions. Thanks again 👍🏾
Sir Whatever i learn about vba with deep knowledge is just because of you i recently buy your book i love the book content and i will definetly join your training program too later when i will have money Whatever i can do for you at this time 🙏🙏🙏 thank you so much sir and love from india 🇮🇳
Excellent and fantastic video. I have been using HTML tags to write tables or ranges in mail item but this is really good and easy. I am really learning a lot from your videos. A BIG THANKS TO YOU. Looking forward for your next video.
Hi Andrew, Another great video! I found that creating another word variable, Dim wdsel as Word.Selection, then Setting wdsel = doc.windows(1).selection helps avoid the whole range method. I would then start a with wdsel statement and use the .insertafter method and the .Move wdParagraph, 1. I love your videos keep them coming 😀
Awesome Andre Sir, I enjoy this training session on VBA Outlook Report. I have a 2 Question 1). I am trying to send this email to others [email] using loop so email sending but missing body Content so how to fix this problem? Question 2) Sir i send report daily using VBA but now I need to reply previous mail to send as today report so is this possible that to send previous day mail send as today like according to me first I will search my latest sent item with subject name [if subject name is same or only date will be different ] than reply that mail and paste today report and change date. like this type.
Hi Pankaj! If you post your code for question 1) I can take a look and see if there's anything wrong. I'm not quite sure how to handle question 2), I don't think I know enough about Outlook to get that to work, I'm sorry!
@@PankajNegi007 Hi Pankaj! You have mi.Send before you write the body of the email in the Word document. Try moving the mi.Send line to the end of your loop, just before Next x I hope it helps!
@@WiseOwlTutorials Thanks Sir for your prompt Reply now coding is working perfecting. Thank you so much sir!!!!! One thing I want to say I learnt all VBA Coding only for 'Wiseowl tutorials'.
Great video. Strange thing is get inspector works on my pc at home, but not at work. Tells me that I have an application or object defined error. It would seem that it's more of an object defined error. I have turned on the correct references/ libraries. So why do I get this error as soon as it gets to the get inspector line. It would be good if I knew this
Hi! We have a whole video on working with embedded chart objects including how to loop through the ChartObjects collection. I hope it will help you ua-cam.com/video/buRwtzVw7UQ/v-deo.html
Can you copy a range of cells with data, paste that into an email, and then copy a picture(s) and put that in the email within the pasted range of data?
Andrew, another brilliant video. I’ve stumbled upon an anomaly when it comes to inserting a very wide table. It appears it will often squeeze columns for various data pints and wrap the text. For example, the table is 6 rows and 8 columns with a comment column on the far right. The start date and end date on columns B and C will squeeze tighter and wrap the years. The table in excel has the proper width, however during the paste process, the 2nd and third columns become thinner. I was hoping to “select the table and have it auto fit or auto fit the widths but am stuck trying to access it in the email displayed.
Hi there, you can reference the table using the Tables property and referring to it by its index number. So after pasting your first table into the email you could say: doc.Tables(1).AutoFitBehavior wdAutoFitContent If you'd prefer your table to occupy the full width of the window, use wdAutoFitWindow rather than wdAutoFitContent I hope that helps and thanks for watching!
Very good. Somewhat works. Much Better than before. It might be a limitation or glitch with excel 2013 and outlook 2013. It works some of the time. What I realized is that I still have to follow up by going to the email, selecting and right click the table, Autofit, then Fixed Column Width to get the desired result. Though rare, I had to select Fixed Column Width twice to obtain the desired result. What vba code Should i use to accomplish the fixed column width for the word table?
Hi there, you can use wdAutoFitFixed instead of wdAutoFitContent or wdAutoFitWindow - you can see more information here docs.microsoft.com/en-us/office/vba/api/word.table.autofitbehavior I hope that helps!
Great to have you back... And thanks for this wonderful video. It was very helpful.. required an add-on to it.. how to format the texts within the body of the email. Like if I want to bold a text or want to color a text how to perform this... Plz help...
Thanks Rajkumar! You can format text in a Word document using a number of different properties. The best approach perhaps is to use Word's macro recorder - change a bunch of formatting options and see what code is written for you. To get you started, here's a bit of code to format the first four characters in the document: With doc.Range(0, 4).Font .Bold = True .TextColor.RGB = RGB(200, 0, 200) .Underline = wdUnderlineSingle End With I hope that helps!
Awesome communication skill.. One observation is that if we have to send more than 1 email then it gives an error i while sending 2nd (onwards) emails (command failed:4198 ) at the line of paste (when Excel table is copied and needs to paste in the email body)
Hi Andrew. I'm following all your vba instroduction videos. Since Part 29.1 i've been having issued with the .GetInspector line. It gives me a run time error. In this example, the wrror comes on the line: Set doc = mi.GetInspector.WordEditor . I've looked for amswers on this and I've had no success. Is there a way that you could help me with this? Either way thanks you all your amazing content!
Hello, Me again. I tried following your instruction to create an outlook email but I get an error message... I downloaded the file you shared on your website then with the code complete and I still got the same error message :"Application-defined or object-defined error" on the line :Set doc = mi.GetInspector.WordEditorWould you know how to solve this issue?(Both library from Word and Outlook are activated when trying to run the sub)Thank you very much in advance =)
Couldn’t hurt to try this. Instead of using doc = mi.GetInspector Try doc = ol.CreateItem(olMailItem).GetInspector In other words limit the number of items set within a set... Maybe excel 2013 has an issue with too many imbedded sets. Don’t know if it worked because I had a typo but I recall a similar issue and to troubleshoot I “spelled out” the entire item/object set and I did not receive an error. Maybe that helps.
Great Video! I do have a question on how could we add attachment in between the body text? Like your video have shown, we could paste table in between text but is it possible to add attachment in between text under Rich Text format? Thank you
Hi Jonathan, you should be able to use the Position parameter of the Attachments.Add method to do this as described here docs.microsoft.com/en-us/office/vba/api/outlook.attachments.add I hope it helps!
@@jonathanchan7377 Hi Jonathan, you can use the Font ColorIndex property to do this docs.microsoft.com/en-us/office/vba/api/word.font.colorindex I hope it helps!
Hi Andrew! I have a question that I think would be interesting. I'm trying to paste a range as an Enhanced Metafile on the email (an image). Do you happen to know how to do this? Is there a way to do this with the word editor? As always, keep at it with your great job!
Thanks Juan Pablo! You can use the PasteSpecial method in Word to indicate that you want to paste the Excel table as a metafile. Using the same workbook shown in the video you can do this: Sheet1.Range("A1").CurrentRegion.Copy doc.Range(1, 1).PasteSpecial _ Placement:=wdInLine, _ DataType:=WdPasteDataType.wdPasteEnhancedMetafile Here's the reference to the PastseSpecial method for some further reading docs.microsoft.com/en-us/office/vba/api/word.range.pastespecial I hope it helps!
@@WiseOwlTutorials Hi Andrew! First of all, thanks for the reply! I've tried to use the PasteSpecial Method and I got a compile error on the Placement parameter wdInLine. The thing is, I'm trying to have a late binding approach on this macro because I don't want it to fail whenever the word and outlook versions change. So I'm sure I got that error because I don't have the word reference enabled. Here is my code: Sub SendEmails() Dim Olook As Object Dim Omail As Object Dim Insp As Object Dim wdDoc As Object Dim Body As String Dim p As Variant Set Olook = CreateObject("Outlook.Application") O.Range("Ini").CurrentRegion.Name = "Overdues"
For Each p In A.Range("LPJM") Set Omail = Olook.Createitem(0) A.Range("AuxLeader").Value = p.Value O.Range("Overdues").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=A.Range("Crit") _ , CopyToRange:=F.Range("A1:U1"), Unique:=False FormatTable With Omail .Bodyformat = 2 .Display .To = F.Range("IniF").Offset(1, 20).Value .Subject = "Overdues CW " & Application.WorksheetFunction.WeekNum(Date) - 1 End With Set Insp = Omail.GetInspector Set wdDoc = CreateObject("Word.Document")
Body = "Dear " & Right(F.Range("IniF").Offset(1, 10).Value, Len(F.Range("IniF").Offset(1, 10).Value) - InStrRev(F.Range("IniF").Offset(1, 10).Value, ",")) ' & "," & vbNewLine & vbNewLine wdDoc.Range.InsertBefore Body F.Range("IniF").CurrentRegion.CopyPicture wdDoc.Range(Len(Body), Len(Body)).Paste Next p End Sub The code leaves me with a new email with the Subject and the To fields with info and my signature. Also I see an open word document containing what should have been the email's body. Maybe the late binding applied to me word inspector is wrong but I'm not sure about it.
@@WiseOwlTutorials I got some updates! I tried using the constants for the parameters on the PasteSpecial function, and now it worked! I now get an email with the body filled with my message and my table. My current problem now is that the table gets pasted on a really small size. Would there be a way to resize this image? Also, I'm trying to add a "Best Regards," at the end of all, but the code inserts this part right next to my image. I could not figure out how to make it show two lines below the table. Here is my code in case I did not make myself clear! Sub SendEmails()
Dim Olook As Object Dim Omail As Object Dim Insp As Object Dim wdDoc As Object Dim Body, Regards, NewLine As String Dim p As Variant
Set Olook = CreateObject("Outlook.Application")
O.Range("Ini").CurrentRegion.Name = "Overdues"
For Each p In A.Range("LPJM") Set Omail = Olook.Createitem(0) A.Range("AuxLeader").Value = p.Value
Body = "Dear " & Right(F.Range("IniF").Offset(1, 10).Value, Len(F.Range("IniF").Offset(1, 10).Value) - InStrRev(F.Range("IniF").Offset(1, 10).Value, ",")) & "," & Chr(10) & Chr(10) & "Please your support to update in Lander below LOPs of " & F.Range("IniF").Offset(1, 4).Value & " as they are already in Overdue, one of them since more than " & Application.WorksheetFunction.RoundDown(Application.WorksheetFunction.Max(F.Range(F.Cells(2, 2), F.Cells(100, 2))), 0) & " weeks." & Chr(10) & Chr(10)
@@juanpablohorn6642 Hi Juan Pablo, try inserting the "Best Regards" first and then the NewLine, to use your style of code it would look like this: Regards = "Best Regards," wdDoc.Range(0, 0).InsertAfter Regards
NewLine = Chr(10) wdDoc.Range(0, 0).InsertAfter NewLine I hope that helps!
@@WiseOwlTutorials! Hi Andrew! It worked! Now I only need to solve the table size on the image. Do you happen to have an idea? As always, thanks a lot for your reply! I really appreciate it!
Great Video. I have a question regarding the Dim doc as word.document set doc = outlook_email.GetInspector.WordEditor Is there a way to use late binding for that? Thank you, Danny
Hi Danny, absolutely, yes! You can just change this line: Dim doc as word.document To this: Dim doc as Object You can then remove the reference to the Word Object Library and everything else will work as normal. I hope that helps!
Hi! You might find these links useful docs.microsoft.com/en-us/office/vba/word/concepts/customizing-word/applying-formatting-to-text docs.microsoft.com/en-us/office/vba/api/word.font.bold docs.microsoft.com/en-us/office/vba/api/word.hyperlinks.add I hope it helps!
i created something like this, but i am taking email body from .docx . but I have to crate personalized emails , for example its name have to change according to data from excel cell.but I cant find something like this on the internet.
Hi Mehmet! There are lots of different ways to do this sort of thing. Try Googling for "excel vba mail merge outlook" and you'll find plenty of suggestions. I hope that helps!
getting error 5097 recently and have no way around it other then going into debug mode and then clicking the email item and then resume the code. any ideas?
Hi there! Sorry I don't have any insights, I'm sure you've Googled but just in case you hadn't seen these suggestions answers.microsoft.com/en-us/msoffice/forum/all/vba-word-has-encountered-a-problem-5097/e060b681-c6da-4ee7-956c-dd2169430e43 social.msdn.microsoft.com/Forums/en-US/2de7d27b-5951-4d32-9ec2-880457679656/getting-a-lot-of-error-5097-in-vba?forum=worddev
Hi , Thanks for your great tutorial. I'm trying to implement this and changing font sizes . It seems to work but only on LTR fonts. I'm trying to get my email to be RTL and control font size face.
Set objSel = objDoc.Windows(1).Selection ' Set objSel = objDoc.Windows(1).Selection With objSel .Collapse wdCollapseStart .Font.Name = "Arial" .Font.Bold = False .Font.Size = 16 .InsertBefore "שלום"
.Font.color = wdColorRed End With objSel.ParagraphFormat.Alignment = 2 In the above the hebrews can be changed the size remains 11, if i insert english text the font size changes to required size . Any suggestions?? Thanks Meir
@@WiseOwlTutorials Thanks for the swift response and question . The insert after helps me with a different issue i was having that I after the email was created I wanted the user to be able to continue to edit (RTL) after the initial text i pushed. Now that I place text with the "InsertAfter" he can continue typing as normal. But as for being able to control the Text Size still the size of the text remains as Outlook Defaults no matter what i seem to do .. Regards
Hi Nelson, here are some basic formatting options you can apply to a range of text. The code in the With block will format the text "Dear Someone": MsgText = _ "Dear Someone," & vbNewLine & vbNewLine _ & "Please see the table below:" & vbNewLine doc.Range(0, 0).InsertBefore Text:=MsgText
With doc.Range(0, 12) .Bold = True .Underline = wdUnderlineSingle .Italic = True .Font.TextColor.RGB = RGB(241, 132, 31) End With
@@WiseOwlTutorials Hi, i have a similar question. If i want to change the font of the entire email, i tried using".WholeStory", it managed to change the font but it will always fail on the very first email i sent out in a loop. Could you advice if there are other way to do it? Thanks.
I think the problem is between 2019 and 2021. Get inspector. Wordrditor has a problem working in excel 2019. App defined or object defined error. No problem in 2021. I have all reference checked. Word and outlook library. Also OLE automation
Interesting, I don't recall having that problem with Office 2019 but we've been using Microsoft 365 for a few years now. I'm not sure what to suggest to solve the problem.
I don't know the answer but you could try some of these suggestions stackoverflow.com/questions/45376329/mailitem-getinspector-wordeditor-in-office-2016-generates-application-defined-or
@@WiseOwlTutorials Thanks for replying. I suspect our IT dept. Has made a change in the background at is it was working before. I have the word and outlook references checked. Clutchibg at straws but: IsWordMail = True ActiveInspector.EditorType = olEditorWord. IsOutTrusted = False. I have also tried preceding Set doc = mi.GetInspector.WordEditor with mi.GetInspector.Activate to no avail. I can only think it must be a change in the background 🤔
Was only revisiting your previous videos the other day, great to have you back, best VBA tutorials by a country mile.
Welcome back Andrew.
As always, thank you for sharing your knowledge. You walked us through the procedure, showed us some of the problems we could encounter and gave us nice and quick solutions.
Thanks again 👍🏾
i realize Im kinda off topic but do anyone know a good place to watch newly released tv shows online?
@Anders Briar Flixportal :D
@Jefferson Lyle Thank you, signed up and it seems to work =) Appreciate it !
@Anders Briar No problem xD
Sir Whatever i learn about vba with deep knowledge is just because of you i recently buy your book i love the book content and i will definetly join your training program too later when i will have money
Whatever i can do for you at this time 🙏🙏🙏 thank you so much sir and love from india 🇮🇳
Sir i can not describe in words that how i am waiting for that day when i will join your training programme
i am so excited to meet you
Dear Andrew
Thank you very much for publishing this new lesson.
Your videos are the best and I hope new ones will be made soon.
Welcome back Andrew, missed ya. Thanks so much.
So grateful that you are back! Thank you so much for sharing your knowledge yet again. It is so valuable :)
Excellent and fantastic video. I have been using HTML tags to write tables or ranges in mail item but this is really good and easy. I am really learning a lot from your videos. A BIG THANKS TO YOU. Looking forward for your next video.
Hey just wanted to say thanks for the SQL Server series! I’ve learned so much and I am hoping to become a back end developer!
Thanks man appreciate it! My uncle used your videos as well and is currently a developer after he changed careers. He recommended I pursue this too!
It's nice to see you again!
Thank you very much Andrew. Found this after a lot of googling and directly addressed my requirement!
best ever vba tutorials.
Thanks Dee, glad you enjoy them!
Oh... It's been a while.... Happy to see a new video... really
Hi Andrew,
Another great video! I found that creating another word variable, Dim wdsel as Word.Selection, then Setting wdsel = doc.windows(1).selection helps avoid the whole range method. I would then start a with wdsel statement and use the .insertafter method and the .Move wdParagraph, 1. I love your videos keep them coming 😀
Hi Jonathan, that's a great tip, thanks for sharing it with us!
You are a saviour for me.
Happy to help, thanks for watching!
very clear video and organize tutor. now I know why my tutor recommends you.
I always waiting for two things one for sequel of American pie and your video😀😀😀😀
Awesome Andre Sir, I enjoy this training session on VBA Outlook Report. I have a 2 Question 1). I am trying to send this email to others [email] using loop so email sending but missing body Content so how to fix this problem? Question 2) Sir i send report daily using VBA but now I need to reply previous mail to send as today report so is this possible that to send previous day mail send as today like according to me first I will search my latest sent item with subject name [if subject name is same or only date will be different ] than reply that mail and paste today report and change date. like this type.
Hi Pankaj! If you post your code for question 1) I can take a look and see if there's anything wrong. I'm not quite sure how to handle question 2), I don't think I know enough about Outlook to get that to work, I'm sorry!
@@WiseOwlTutorials This is my code
Sub SendMailwithLoop()
Dim x As Long
Dim lastrow As Long
Dim rmname As String
On Error Resume Next
Sheet3.Select
lastrow = Sheet3.Range("A1", Range("A1").End(xlDown)).Count
For x = 2 To lastrow
Dim ol As Outlook.Application
Dim mi As Outlook.MailItem
Set ol = New Outlook.Application
Set mi = ol.CreateItem(olMailItem)
Dim doc As Word.Document
Set doc = mi.GetInspector.WordEditor
mi.Display
mi.To = Sheet1.Range("D3")
mi.CC = Sheet1.Range("D4")
mi.Subject = "No Closure " & Sheet3.Cells(x, 1)
'mi.Send
Dim Msgtext As Stringa
' rmname = Cells(x, 1).Value
' Sheet1.Activate
Sheet3.Cells(x, 1).Copy
Sheet2.Range("B1").PasteSpecial
Sheet2.Activate
Sheet2.Range("A1").CurrentRegion.Copy
doc.Range(0, 0).Paste
Msgtext = "Dear Team, " & vbNewLine & vbNewLine & _
"xxxxxxxxxxx"
doc.Range(0, 1).InsertBreak
doc.Range.InsertBefore Msgtext
Application.CutCopyMode = False
Next x
End Sub
this code working in break mode when i use normal mail send but body missing sir kindly help me
@@PankajNegi007 Hi Pankaj! You have mi.Send before you write the body of the email in the Word document. Try moving the mi.Send line to the end of your loop, just before Next x
I hope it helps!
@@WiseOwlTutorials Thanks Sir for your prompt Reply now coding is working perfecting. Thank you so much sir!!!!! One thing I want to say I learnt all VBA Coding only for 'Wiseowl tutorials'.
Great video. Strange thing is get inspector works on my pc at home, but not at work. Tells me that I have an application or object defined error. It would seem that it's more of an object defined error. I have turned on the correct references/ libraries. So why do I get this error as soon as it gets to the get inspector line. It would be good if I knew this
Thank you ..this helped me a lot
Can you advise how to highlight text
Really, really helpful video! Thank you!
You're welcome, Miguel! Thanks so much for watching!
Why is Outlook attachments.add id not working when using WordEditor in VBA ? Its attaching the files in Inline rather than attachment bar.
Amazing video! Any guidance on how to insert multiple charts from the same worksheet?
Hi! We have a whole video on working with embedded chart objects including how to loop through the ChartObjects collection. I hope it will help you ua-cam.com/video/buRwtzVw7UQ/v-deo.html
Can you copy a range of cells with data, paste that into an email, and then copy a picture(s) and put that in the email within the pasted range of data?
Andrew, another brilliant video. I’ve stumbled upon an anomaly when it comes to inserting a very wide table. It appears it will often squeeze columns for various data pints and wrap the text. For example, the table is 6 rows and 8 columns with a comment column on the far right. The start date and end date on columns B and C will squeeze tighter and wrap the years. The table in excel has the proper width, however during the paste process, the 2nd and third columns become thinner. I was hoping to “select the table and have it auto fit or auto fit the widths but am stuck trying to access it in the email displayed.
Hi there, you can reference the table using the Tables property and referring to it by its index number. So after pasting your first table into the email you could say:
doc.Tables(1).AutoFitBehavior wdAutoFitContent
If you'd prefer your table to occupy the full width of the window, use wdAutoFitWindow rather than wdAutoFitContent
I hope that helps and thanks for watching!
Very good. Somewhat works. Much Better than before. It might be a limitation or glitch with excel 2013 and outlook 2013.
It works some of the time. What I realized is that I still have to follow up by going to the email, selecting and right click the table, Autofit, then Fixed Column Width to get the desired result.
Though rare, I had to select Fixed Column Width twice to obtain the desired result.
What vba code Should i use to accomplish the fixed column width for the word table?
Hi there, you can use wdAutoFitFixed instead of wdAutoFitContent or wdAutoFitWindow - you can see more information here docs.microsoft.com/en-us/office/vba/api/word.table.autofitbehavior
I hope that helps!
Sir by god u r awesome
Great video. Thanks for sharing with us.
Hi i actuaclly want to insert a hyperlink in as existing email template how can i insert the link in an existing email template?
Hi Andrew- what is the book you wrote. Is it available in Amazon? Please suggest
If I use
Set doc=mi.getinspector.wordeditor
Doc.range(0,0). Paste
Will paste all word content into email body???
Great to have you back... And thanks for this wonderful video. It was very helpful.. required an add-on to it.. how to format the texts within the body of the email. Like if I want to bold a text or want to color a text how to perform this... Plz help...
Thanks Rajkumar! You can format text in a Word document using a number of different properties. The best approach perhaps is to use Word's macro recorder - change a bunch of formatting options and see what code is written for you. To get you started, here's a bit of code to format the first four characters in the document:
With doc.Range(0, 4).Font
.Bold = True
.TextColor.RGB = RGB(200, 0, 200)
.Underline = wdUnderlineSingle
End With
I hope that helps!
@@WiseOwlTutorials thanks for your reply Sir... Surely will try this...
Awesome communication skill.. One observation is that if we have to send more than 1 email then it gives an error i while sending 2nd (onwards) emails (command failed:4198 ) at the line of paste (when Excel table is copied and needs to paste in the email body)
Hmm interesting, I don't see the same results. Did you discover why this is happening?
Hi Andrew. I'm following all your vba instroduction videos. Since Part 29.1 i've been having issued with the .GetInspector line. It gives me a run time error. In this example, the wrror comes on the line: Set doc = mi.GetInspector.WordEditor . I've looked for amswers on this and I've had no success. Is there a way that you could help me with this? Either way thanks you all your amazing content!
Thanks it worked ❤
You're welcome, thanks for watching!
Hello,
Me again. I tried following your instruction to create an outlook email but I get an error message...
I downloaded the file you shared on your website then with the code complete and I still got the same error message :"Application-defined or object-defined error" on the line :Set doc = mi.GetInspector.WordEditorWould you know how to solve this issue?(Both library from Word and Outlook are activated when trying to run the sub)Thank you very much in advance =)
Even I get the same error. I have only one version of office. Have tried separately on Standard 2013 and Professional plus 2013.
Couldn’t hurt to try this. Instead of using doc = mi.GetInspector
Try doc = ol.CreateItem(olMailItem).GetInspector
In other words limit the number of items set within a set...
Maybe excel 2013 has an issue with too many imbedded sets.
Don’t know if it worked because I had a typo but I recall a similar issue and to troubleshoot I “spelled out” the entire item/object set and I did not receive an error. Maybe that helps.
@@TitoLatino Tried that too.still the same error. Is it because macros on my outlook are disabled?
@Sairam good question. I honestly Don’t know. I’m not as knowledgeable as the WiseOwl himself.
Great Video! I do have a question on how could we add attachment in between the body text? Like your video have shown, we could paste table in between text but is it possible to add attachment in between text under Rich Text format? Thank you
Hi Jonathan, you should be able to use the Position parameter of the Attachments.Add method to do this as described here docs.microsoft.com/en-us/office/vba/api/outlook.attachments.add
I hope it helps!
@@WiseOwlTutorials Thank you so much for the advice. It Works! Really appreciate your prompt reply.
@@jonathanchan7377 No problem Jonathan, happy to hear that it worked!
@@WiseOwlTutorials Sorry, come across another question. How could we change the font color of some part of the text in the email body?
@@jonathanchan7377 Hi Jonathan, you can use the Font ColorIndex property to do this
docs.microsoft.com/en-us/office/vba/api/word.font.colorindex
I hope it helps!
Hi Andrew! I have a question that I think would be interesting. I'm trying to paste a range as an Enhanced Metafile on the email (an image). Do you happen to know how to do this? Is there a way to do this with the word editor? As always, keep at it with your great job!
Thanks Juan Pablo! You can use the PasteSpecial method in Word to indicate that you want to paste the Excel table as a metafile. Using the same workbook shown in the video you can do this:
Sheet1.Range("A1").CurrentRegion.Copy
doc.Range(1, 1).PasteSpecial _
Placement:=wdInLine, _
DataType:=WdPasteDataType.wdPasteEnhancedMetafile
Here's the reference to the PastseSpecial method for some further reading docs.microsoft.com/en-us/office/vba/api/word.range.pastespecial
I hope it helps!
@@WiseOwlTutorials Hi Andrew! First of all, thanks for the reply!
I've tried to use the PasteSpecial Method and I got a compile error on the Placement parameter wdInLine. The thing is, I'm trying to have a late binding approach on this macro because I don't want it to fail whenever the word and outlook versions change. So I'm sure I got that error because I don't have the word reference enabled. Here is my code:
Sub SendEmails()
Dim Olook As Object
Dim Omail As Object
Dim Insp As Object
Dim wdDoc As Object
Dim Body As String
Dim p As Variant
Set Olook = CreateObject("Outlook.Application")
O.Range("Ini").CurrentRegion.Name = "Overdues"
For Each p In A.Range("LPJM")
Set Omail = Olook.Createitem(0)
A.Range("AuxLeader").Value = p.Value
O.Range("Overdues").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=A.Range("Crit") _
, CopyToRange:=F.Range("A1:U1"), Unique:=False
FormatTable
With Omail
.Bodyformat = 2
.Display
.To = F.Range("IniF").Offset(1, 20).Value
.Subject = "Overdues CW " & Application.WorksheetFunction.WeekNum(Date) - 1
End With
Set Insp = Omail.GetInspector
Set wdDoc = CreateObject("Word.Document")
Body = "Dear " & Right(F.Range("IniF").Offset(1, 10).Value, Len(F.Range("IniF").Offset(1, 10).Value) - InStrRev(F.Range("IniF").Offset(1, 10).Value, ",")) ' & "," & vbNewLine & vbNewLine
wdDoc.Range.InsertBefore Body
F.Range("IniF").CurrentRegion.CopyPicture
wdDoc.Range(Len(Body), Len(Body)).Paste
Next p
End Sub
The code leaves me with a new email with the Subject and the To fields with info and my signature. Also I see an open word document containing what should have been the email's body. Maybe the late binding applied to me word inspector is wrong but I'm not sure about it.
@@WiseOwlTutorials I got some updates! I tried using the constants for the parameters on the PasteSpecial function, and now it worked! I now get an email with the body filled with my message and my table. My current problem now is that the table gets pasted on a really small size. Would there be a way to resize this image? Also, I'm trying to add a "Best Regards," at the end of all, but the code inserts this part right next to my image. I could not figure out how to make it show two lines below the table.
Here is my code in case I did not make myself clear!
Sub SendEmails()
Dim Olook As Object
Dim Omail As Object
Dim Insp As Object
Dim wdDoc As Object
Dim Body, Regards, NewLine As String
Dim p As Variant
Set Olook = CreateObject("Outlook.Application")
O.Range("Ini").CurrentRegion.Name = "Overdues"
For Each p In A.Range("LPJM")
Set Omail = Olook.Createitem(0)
A.Range("AuxLeader").Value = p.Value
O.Range("Overdues").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=A.Range("Crit") _
, CopyToRange:=F.Range("A1:U1"), Unique:=False
FormatTable
With Omail
.Bodyformat = 2
.Display
.To = F.Range("IniF").Offset(1, 20).Value
.Subject = "Overdues CW " & Application.WorksheetFunction.WeekNum(Date) - 1
End With
Set Insp = Omail.GetInspector
Set wdDoc = Insp.WordEditor
' NewLine = Chr(10)
' wdDoc.Range(1, 0).InsertAfter NewLine
'
' Regards = "Best Regards,"
' wdDoc.Range(0, 0).InsertAfter Regards
F.Range("IniF").CurrentRegion.Copy
wdDoc.Range(0, 0).PasteSpecial _
Placement:=0, _
DataType:=9
Body = "Dear " & Right(F.Range("IniF").Offset(1, 10).Value, Len(F.Range("IniF").Offset(1, 10).Value) - InStrRev(F.Range("IniF").Offset(1, 10).Value, ",")) & "," & Chr(10) & Chr(10) & "Please your support to update in Lander below LOPs of " & F.Range("IniF").Offset(1, 4).Value & " as they are already in Overdue, one of them since more than " & Application.WorksheetFunction.RoundDown(Application.WorksheetFunction.Max(F.Range(F.Cells(2, 2), F.Cells(100, 2))), 0) & " weeks." & Chr(10) & Chr(10)
wdDoc.Range.InsertBefore Text:=Body
Next p
End Sub
@@juanpablohorn6642 Hi Juan Pablo, try inserting the "Best Regards" first and then the NewLine, to use your style of code it would look like this:
Regards = "Best Regards,"
wdDoc.Range(0, 0).InsertAfter Regards
NewLine = Chr(10)
wdDoc.Range(0, 0).InsertAfter NewLine
I hope that helps!
@@WiseOwlTutorials! Hi Andrew! It worked! Now I only need to solve the table size on the image. Do you happen to have an idea?
As always, thanks a lot for your reply! I really appreciate it!
Hi Andrew, I have found the column width and the row height of the table get changed on Outlook after pasting. Any ideas? Thanks
Hi Margaret, you might find this video useful ua-cam.com/video/VbE_WtAdjos/v-deo.html
I hope it helps!
@@WiseOwlTutorials Thanks Andrew! Appreciated.
Thank You!
You're welcome, Didzis! Thank you for watching!
Great Video.
I have a question regarding the
Dim doc as word.document
set doc = outlook_email.GetInspector.WordEditor
Is there a way to use late binding for that?
Thank you,
Danny
Hi Danny, absolutely, yes! You can just change this line:
Dim doc as word.document
To this:
Dim doc as Object
You can then remove the reference to the Word Object Library and everything else will work as normal.
I hope that helps!
@@WiseOwlTutorials Thank you, Danny
How to bold a text and add a hyperlink using this procedure?
Hi! You might find these links useful
docs.microsoft.com/en-us/office/vba/word/concepts/customizing-word/applying-formatting-to-text
docs.microsoft.com/en-us/office/vba/api/word.font.bold
docs.microsoft.com/en-us/office/vba/api/word.hyperlinks.add
I hope it helps!
Very useful!
i created something like this, but i am taking email body from .docx . but I have to crate personalized emails , for example its name have to change according to data from excel cell.but I cant find something like this on the internet.
Hi Mehmet! There are lots of different ways to do this sort of thing. Try Googling for "excel vba mail merge outlook" and you'll find plenty of suggestions.
I hope that helps!
@@WiseOwlTutorials thank you very much!
@@mecann1 No problem!
getting error 5097 recently and have no way around it other then going into debug mode and then clicking the email item and then resume the code. any ideas?
Hi there! Sorry I don't have any insights, I'm sure you've Googled but just in case you hadn't seen these suggestions
answers.microsoft.com/en-us/msoffice/forum/all/vba-word-has-encountered-a-problem-5097/e060b681-c6da-4ee7-956c-dd2169430e43
social.msdn.microsoft.com/Forums/en-US/2de7d27b-5951-4d32-9ec2-880457679656/getting-a-lot-of-error-5097-in-vba?forum=worddev
Hi , Thanks for your great tutorial. I'm trying to implement this and changing font sizes . It seems to work but only on LTR fonts. I'm trying to get my email to be RTL and control font size face.
Set objSel = objDoc.Windows(1).Selection
'
Set objSel = objDoc.Windows(1).Selection
With objSel
.Collapse wdCollapseStart
.Font.Name = "Arial"
.Font.Bold = False
.Font.Size = 16
.InsertBefore "שלום"
.Font.color = wdColorRed
End With
objSel.ParagraphFormat.Alignment = 2
In the above the hebrews can be changed the size remains 11, if i insert english text the font size changes to required size . Any suggestions??
Thanks
Meir
Hi Meir, that's interesting! The solution isn't just as simple as using InsertAfter rather than InsertBefore is it? (he asked hopefully!)
@@WiseOwlTutorials Thanks for the swift response and question . The insert after helps me with a different issue i was having that I after the email was created I wanted the user to be able to continue to edit (RTL) after the initial text i pushed. Now that I place text with the "InsertAfter" he can continue typing as normal. But as for being able to control the Text Size still the size of the text remains as Outlook Defaults no matter what i seem to do ..
Regards
Thank u so much!
You're very welcome, thanks for watching!
How can I include formatting like bold and underline?
Hi Nelson, here are some basic formatting options you can apply to a range of text. The code in the With block will format the text "Dear Someone":
MsgText = _
"Dear Someone," & vbNewLine & vbNewLine _
& "Please see the table below:" & vbNewLine
doc.Range(0, 0).InsertBefore Text:=MsgText
With doc.Range(0, 12)
.Bold = True
.Underline = wdUnderlineSingle
.Italic = True
.Font.TextColor.RGB = RGB(241, 132, 31)
End With
@@WiseOwlTutorials Hi, i have a similar question. If i want to change the font of the entire email, i tried using".WholeStory", it managed to change the font but it will always fail on the very first email i sent out in a loop. Could you advice if there are other way to do it? Thanks.
I think the problem is between 2019 and 2021. Get inspector. Wordrditor has a problem working in excel 2019. App defined or object defined error. No problem in 2021. I have all reference checked. Word and outlook library. Also OLE automation
Interesting, I don't recall having that problem with Office 2019 but we've been using Microsoft 365 for a few years now. I'm not sure what to suggest to solve the problem.
@@WiseOwlTutorials lucky you. I just thought this morning that my firm has disabled some VBA functionality. Is that possible
3 godziny siedzę, kombinuje z jakimiś htmlami, funkcjami itp a to takie banalne 🤦♂ THX 👍👍
doc is an object variable of Word.Document and oOutlokkItm is an object variable of Outlook.MailItem thank you
so just setting Set doc = oOutLokkItm.GetInspector.WordEditor gives me the error code number 287 in VBA and i have no clue how to solve it. Thanks
I don't know the answer but you could try some of these suggestions stackoverflow.com/questions/45376329/mailitem-getinspector-wordeditor-in-office-2016-generates-application-defined-or
@@WiseOwlTutorials I receive the same error with this line of code and can't figure out why
@@mikesmith8346 Sorry Mike, I don't know the answer to this one.
@@WiseOwlTutorials Thanks for replying. I suspect our IT dept. Has made a change in the background at is it was working before.
I have the word and outlook references checked. Clutchibg at straws but:
IsWordMail = True
ActiveInspector.EditorType = olEditorWord.
IsOutTrusted = False.
I have also tried preceding
Set doc = mi.GetInspector.WordEditor with
mi.GetInspector.Activate to no avail.
I can only think it must be a change in the background 🤔
@@mikesmith8346 Hi Mike, you may be right, I'm unable to reproduce the error here.