CODEWITHSUNDEEP
excelvbaoutlook
Gaus Shaikh
Gaus Shaikh

Reputation: 209

Paste Excel range in center of body of Outlook email

I copy a range and paste into Outlook email with signature.

I want to reflect the range in center of body of email.

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Set rng = Nothing
Set rng = ThisWorkbook.Sheets("Output").Range("D7:E18")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
    .To = ""
    .BCC = ""
    .Subject = "Subject"
    .Display
    Dim wdDoc As Object
    Dim wdRange As Object
    Set wdDoc = OutMail.GetInspector.WordEditor
    Set wdRange = wdDoc.Range(0, 0)
    wdRange.InsertAfter vbCrLf & vbCrLf
    rng.Copy
    wdRange.Paste
    Set OutMail = Nothing
    Set OutApp = Nothing
End With

Upvotes: 0

Views: 694

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

What the code does is, it centers not only the text but also the table (Excel Range) pasted in the outlook body.

Option Explicit

Const wdAlignRowCenter As Integer = 1
Const wdAlignParagraphCenter As Integer = 1

Sub Sample()
    Dim OutApp As Object, OutMail As Object
    Dim wdDoc As Object, wdRange As Object
    Dim rng As Range
    Dim i As Long
    
    Set rng = ThisWorkbook.Sheets("Output").Range("D7:E18")
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    
    With OutMail
        .To = ""
        .BCC = ""
        .Subject = "Subject"
        .Display
    
        Set wdDoc = .GetInspector.WordEditor
        Set wdRange = wdDoc.Range(0, 0)
        
        wdRange.InsertAfter vbCrLf & vbCrLf
        rng.Copy
        wdRange.Paste
        
        DoEvents
    
        Set wdRange = wdDoc.Range(0, wdDoc.Characters.Count)
    
        wdRange.ParagraphFormat.Alignment = wdAlignParagraphCenter
        
        For i = 1 To wdRange.Tables.Count
            wdRange.Tables(i).Rows.Alignment = wdAlignRowCenter
        Next i
    End With
    
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

Note:

  1. BTW Set wdDoc = .GetInspector.WordEditor will give you error if the default editor is not MS Word...
  2. If there are images in your body then you may have to handle them as well seperately

Upvotes: 1

Related Questions