VBA code to Align out look massage center


New Member
Dec 22, 2014
I have macro to send mail from excel sheet but I don't know the proper code to change align center or right

VBA Code:
Sub CreateAndSend()
Dim OutApp As Object, OutMail As Object
Dim LastRow As Long, StudRow As Long, VarRow As Long
Dim Email As String, Subj As String, Mesg As String, FileName As String

With Sheet2
        LastRow = Sheet1.Range("C1200").End(xlUp).Row  'Last Row
        For StudRow = 13 To LastRow 'Loop through Students
                If Sheet1.Range("K" & StudRow).Value = Empty Then 'Only run if Certificate Created On Date is blank
                            .Range("B4").Value = StudRow 'Set Student Row
                            .Calculate 'Force Calculation on all formulas
                            'Check For Send Email & Email Address
                            If InStr(Sheet1.Range("J" & StudRow).Value, "Email") > 0 And Sheet1.Range("G" & StudRow).Value <> Empty Then
                                                Email = Sheet1.Range("G" & StudRow).Value 'Email Address
                                                Subj = .Range("I4").Value 'Original Subject
                                                Mesg = .Range("I5").Value 'Original Message
                                                'Replace Variables in Message and Subject
                                                 For VarRow = 5 To 13
                                                    Subj = Replace(Subj, .Range("C" & VarRow).Value, .Range("B" & VarRow).Value)
                                                    Mesg = Replace(Mesg, .Range("C" & VarRow).Value, .Range("B" & VarRow).Value)
                                                 Next VarRow
                                                FileName = ThisWorkbook.Path & "/" & "Certificate.pdf" 'Create Temporary PDF File name
                                                On Error Resume Next
                                                Kill (FileName) 'Delete Certificate if it exists
                                                On Error GoTo 0
                                                .ExportAsFixedFormat xlTypePDF, FileName, , , False, 1, 1, False 'Create PDF To Email
                                                Set OutApp = CreateObject("Outlook.Application") 'Sets the Outlook Application
                                                Set OutMail = OutApp.CreateItem(0) 'Creates the email
                                                With OutMail
                                                    .To = Email 'Student Email
                                                    .attachments.Add FileName 'Attach Certificate
                                                    .Subject = Subj 'Create Subject
                                                    .Body = Mesg 'Create Message
                                                    '.Align = "center"
                                                    .display 'Change to .Send to Send emails without displaying them first
                                                End With
                                                On Error GoTo 0
                                                Set OutMail = Nothing
                                                On Error Resume Next    'If PDF is open, it cannot be deleted
                                                Kill (FileName) 'Delete Temporary Certificate
                                End If 'End If Email Option Selected & Email address exists
                'Check For Printing
                 If InStr(Sheet1.Range("J" & StudRow).Value, "Print") > 0 Then .PrintOut , , , False, , False, , , False
                 Sheet1.Range("K" & StudRow).Value = Now 'Set Certificate send on Date & Time
                End If 'End If Students Certificate Created
         Next StudRow
End With

End Sub


  • Align center.PNG
    Align center.PNG
    63.4 KB · Views: 4

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Trevor G

Well-known Member
Jul 17, 2008
Office Version
  1. 2016
  1. Windows
One way is to use HTMLBody then you can use the <centre> tag.

VBA Code:
.htmlBody = "<HTML><center>mesg</center></HTML>"

Watch MrExcel Video

Forum statistics

Latest member