VBA code to Align out look massage center

bahaaomar5

New Member
Joined
Dec 22, 2014
Messages
1
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
 

Attachments

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

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,675
Office Version
  1. 2016
Platform
  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

Threads
1,112,803
Messages
5,542,590
Members
410,561
Latest member
Sasha Lawrence
Top