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