Word -> Outlook Email Body

[beczer]

Hi everyone

I'm new here and VBA beginner but I need to create something what is too advanced for me at this moment. So I hope you can help me.

So what I need:

I want to create a Macro which Export Part of Word Content (Text and Tables) into Outlook Email body. So I think that I have to use Bookmarks in Word as a reference and probably generate HTML tables.

Every hint, advise will be valuable for me.

Thank you in advance !

Tom

 

[beczer]

Is there someone who can help with this issue ?

Thanks

 

[Worf]

This Word macro will transfer text to Outlook. I will be back later with a table example.

' Word macro
Sub emailFromDoc()
Dim olook As Object, editor As Object, oMail As MailItem, bmk As Bookmark
Set bmk = ActiveDocument.Bookmarks("bm2")
bmk.Range.Copy
Set olook = CreateObject("Outlook.Application")
Set oMail = olook.CreateItem(olMailItem)
With oMail
    .BodyFormat = olFormatRichText
    Set editor = .GetInspector.WordEditor
    editor.Content.Paste
    .Display
End With
End Sub

 

[Macropod]

Given your other thread's scope (http://www.mrexcel.com/forum/genera...ns/977084-excel-word-doc-file-patch-cell.html), I have to wonder if you should be using a Word mailmerge to email, or even emailing directly from Excel without involving Word at all.

 

[beczer]

Hi Macropod

Once again thank you for your help. Regarding sending email directly from Excel, let me explain you why I decided to reject this idea and export data into Word.
Some time ago I wanted to create "template" in Excel (Sheet1) and put there data from Sheet2.
But the problem is with rows auto adjust and merged cells.
Let's say I have a "template" in Sheet1 and somewhere in the middle I want to put a data from Sheet2, but data from Sheet2 is not constant. It's mean when I paste data from Sheet2 into Sheet1 then rows in Sheet1 should adjust to the line - in other world I don't want to have a break/empty rows or replace existing data in Sheet1. I know it is possible but the problem is with Merged Cells. Above idea doesn't work with merged cells. That why I think most people need to create "templates" in Word and export data.

I hope it is clear

Thanks

 

[beczer]

Hi Worf
Many thanks for the above code. You helped me a lot!


I would also like to add Possibility put E-mail Subject and Recipient's Email Address from Cells. Is it possible?


Final question: Is it possible to send / save the message as a Draft? I can do manually but is it possible to set it in VBA code?

 

[Worf]

When you say cells, are they Excel cells, Word table cells or something else?

' Word macro
Sub emailFromDoc()
Dim olook As Object, editor As Object, oMail As MailItem, bmk As Bookmark, t As Table
Set t = ActiveDocument.Tables(2)
Set bmk = ActiveDocument.Bookmarks("bm2")
bmk.Range.Copy
Set olook = CreateObject("Outlook.Application")
Set oMail = olook.CreateItem(olMailItem)
With oMail
    .BodyFormat = olFormatRichText
    .Subject = t.Cell(2, 2).Range.Text          ' from table
    .To = t.Cell(3, 3).Range.Text
    Set editor = .GetInspector.WordEditor
    editor.Content.Paste
    .Display
    .Save
'   .Send
End With
End Sub

 

[beczer]

Excel cells

 

[beczer]

One more thing.

Above VBA code transfer Word Doc text to Mail body and it works perfect. My last question is how to add mentioned Word Doc as a PDF attachment.

I have no idea, it is too complex for me but I really need it so I will be so grateful if you help me.

Thanks

 

[Worf]

This example is double automation; Word gets Excel data and transfers it to Outlook.

' Word 2013 macro
Sub emailFromDoc()
Dim olook As Object, editor As Object, oMail As MailItem, bmk As Bookmark, _
xl As Object, wb As Workbook
Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open("c:\pub\erp2.xlsm")
Set bmk = ActiveDocument.Bookmarks("bm2")
bmk.Range.Copy
Set olook = CreateObject("Outlook.Application")
Set oMail = olook.CreateItem(olMailItem)
ActiveDocument.SaveAs2 "c:\pub\doccopy.pdf", wdFormatPDF        ' create PDF
With oMail
    .Display
    .BodyFormat = olFormatRichText
    .Subject = wb.Sheets("users").[d4]                          ' from Excel
    .To = wb.Sheets("users").[d1]
    Set editor = .GetInspector.WordEditor
    editor.Content.Paste
    .Save
    .Attachments.Add "c:\pub\doccopy.pdf", olByValue, 1, "PDF version"
    .Save
'   .Send
End With
Set oMail = Nothing: Set olook = Nothing
Set bmk = Nothing
wb.Close
Set wb = Nothing: Set xl = Nothing
End Sub