Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to Copy Word Text into an Email
Hello,
What I'm trying to do is set up a macro that will copy text from a word doc into the body of my email (the reason a word doc is so I can keep the formatting). Here is what I have so far: Sub SendEmail() Dim OutlookApp As Object Dim MItem As Object Dim cell As Range Dim email As String Dim cc As String Dim subject As String Dim body As String Dim attach As String Dim I As Long 'Create Outlook object Set OutlookApp = CreateObject("Outlook.Application") ' Loop through the rows For Each cell In Range("b2:b100").Cells.SpecialCells(xlCellTypeCons tants) email = cell.Value subject = cell.Offset(0, 2).Value body = cell.Offset(0, 3).Value cc = cell.Offset(0, 1).Value attach = cell.Offset(0, 4).Value 'Create Mail Item and send it Set MItem = OutlookApp.CreateItem(0) With MItem .To = email .cc = cc .subject = subject .body = body .Attachments.Add "C:\temp\test.xls" .Attachments.Add "C:\temp\test2.xls" .display End With Next End Sub Right now the body is just being pulled from a cell in my excel file, but the cell doesn't hold all the formatting I need. If anyone can help me out here that would be great, or if you even have a better idea on how to simply add the text that's cool too. Thanks, Tyson (Sorry if this is a double post - I got an error page the last time I hit Post) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to Copy Word Text into an Email
Tyson,
You could create your body in word, then save it as a webpage (.html, or .htm). Then change your default format in Outlook to HTML. To add the body from the saved file use this function below: Change your line to: ..Body = Get_Body Function Get_Body() as string Dim ie as Object With ie .visible=True .navigate "C:\Your File Name.html" Do Until .ReadyState = 4 Loop Get_Body = .Document.Body.InnerHTML .Quit End With Set ie = Nothing End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to Copy Word Text into an Email
I forgot to set the ie object so use this function instead:
Function Get_Body() as string Dim ie as Object Set ie = CreateObject("InternetExplorer.Application") With ie .visible=True .navigate "C:\Your File Name.html" Do Until .ReadyState = 4 Loop Get_Body = .Document.Body.InnerHTML .Quit End With Set ie = Nothing End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to Copy Word Text into an Email
Thanks Dave, but I think I'm having a brain cramp here - if I read
what you are saying right, this is what the macro should look like: Sub SendEmail() Dim OutlookApp As Object Dim MItem As Object Dim cell As Range Dim email As String Dim cc As String Dim subject As String Dim body As String Dim attach As String Dim I As Long Dim ie As Object Set ie = CreateObject("InternetExplorer.Application") 'Create Outlook object Set OutlookApp = CreateObject("Outlook.Application") ' Loop through the rows For Each cell In Range("b2:b100").Cells.SpecialCells(xlCellTypeCons tants) email = cell.Value subject = cell.Offset(0, 2).Value body = cell.Offset(0, 3).Value cc = cell.Offset(0, 1).Value attach = cell.Offset(0, 4).Value 'Create Mail Item and send it Set MItem = OutlookApp.CreateItem(0) With MItem .To = email .cc = cc .subject = subject .body = Get_Body Function Get_Body() As String Dim ie As Object Set ie = CreateObject("InternetExplorer.Application") With ie .Visible = True .navigate "C:\temp\1.htm" Do Until .ReadyState = 4 Loop Get_Body = .Document.body.InnerHTML .Quit End With Set ie = Nothing End Function .Attachments.Add "C:\temp\test.xls" .Attachments.Add "C:\temp\test2.xls" .display End With Next End Sub But when I run this, I get "Compile error: Expected End Sub" - what am I doing wrong with this code? Thanks again, Tyson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to Copy Word Text into an Email
Tyson,
You need paste the function beneath the End Sub statement. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to Copy Word Text into an Email
Ahh - that makes much more sense... but now I have one more problem.
I have my default email set to HTML but here is how the body comes in at: <DIV class=Section1 <P class=MsoNormal<SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"Good afternoon,<?xml:namespace prefix = o ns = "urn:schemas- microsoft-com:office:office" /<o:p</o:p</SPAN</P Any idea on what is causing this? Thanks, Tyson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro to Copy Word Text into an Email
Solved my own problem - the answer for those of you that ca
.htmlbody = Get_Body That was it - just needed to ass "html" infront of body Tyson On Jun 21, 1:28 pm, wrote: Ahh - that makes much more sense... but now I have one more problem. I have my default email set to HTML but here is how the body comes in at: <DIV class=Section1 <P class=MsoNormal<SPAN style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"Good afternoon,<?xml:namespace prefix = o ns = "urn:schemas- microsoft-com:office:office" /<o:p</o:p</SPAN</P Any idea on what is causing this? Thanks, Tyson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I copy several lines of word text into one excel cell? | New Users to Excel | |||
Copy text from Excel to Word | Excel Programming | |||
Copy text from excel and paste in word - Urgent Help | Excel Programming | |||
how do i find an email and copy the text and paste into excel sheet | Excel Programming | |||
How do I copy a Word text file into Excel in a way that each char. | Excel Discussion (Misc queries) |