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: 27

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
One way is to use HTMLBody then you can use the <centre> tag.

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

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top