Send Ledger Confirmation mail from Excel

[kunal86]

Hi,

I have created VBA to send Ledger Confirmation mail to multiple customers from Excel..

I want to bold Amount in numbers & in Words. Also amount in numbers should be in comma format.

Sub SendEmail()
ActiveWorkbook.Activate
Sheets("Main").Select
Range("A3").Select
Do While ActiveCell.Value <> ""
EmailSender ActiveCell.Offset(0, 6).Value, ActiveCell.Offset(0, 7).Value, ActiveCell.Offset(0, 1).Value, ActiveCell.Offset(0, 0).Value, ActiveCell.Offset(0, 8).Value, ActiveCell.Offset(0, 9).Value, ActiveCell.Offset(0, 2).Value, ActiveCell.Offset(0, 10).Value, ActiveCell.Offset(0, 5).Value, ActiveCell.Offset(0, 4).Value, ActiveCell.Offset(0, 11).Value
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Sub EmailSender(recipient As String, CC As String, Customer As String, CustomerCode As String, Rupees As String, Amount As String, OutsandingDate As String, Regards As String, Location As String, Address As String, DueDate As String)

Dim outlookApp As Object
Dim outlookMailItem As Object
Dim MailBody As String


'Create the Outlook application and the empty email.
Set outlookApp = CreateObject("Outlook.Application")
Set outlookMailItem = outlookApp.CreateItem(0)


MailBody = "Dear Sir," & vbNewLine & vbNewLine _
& "M/s. " & Customer & vbNewLine & Address & vbNewLine & _
Location & vbNewLine & vbNewLine & vbNewLine _
& "With reference to the above subject, our books of account shows a debit balance in your respective account of Rs. " + Rupees + "/- (" + Amount + ") as on " + OutsandingDate + ". " & vbNewLine _
& "Please sign (authorised) the confirmation as mentioned below and hand over to our respective sales representative or reply scan copy on our email ID - accounts@gmail.com" & vbNewLine _
& vbNewLine _
& "In case you need any further assistance do write to us, with the signed copy of this confirmation letter mentioning the amount balance in your books of accounts and statement of accounts to reconcile the differences if any." & vbNewLine _
& vbNewLine _
& "We will aprreciate your earliest attention on this matter. If we do not receive your confirmation on or before " + DueDate + ", we would treat the above balance is correct." & vbNewLine _
& "Yours faithfully," & vbNewLine _
& Regards & vbNewLine _
& "Credit Control" & vbNewLine _
& "ABC Pvt. Ltd." & vbNewLine _
& vbNewLine _
& "---------------------------------------------------------------------------------------------------------------------------------------------" & vbNewLine _
& vbNewLine _
& "Confirmation:" & vbNewLine _
& vbNewLine _
& "We confirm the balance amount Rs." + Rupees + " /- (" + Amount + ") as mention above as per our books of accounts as on " + OutsandingDate + "." & vbNewLine _
& "Date: _ _ _ _ _ _ _ Signature: _ _ _ _ _ _ _ _ _ _ _ Rubber Stamp:" & vbNewLine _
& vbNewLine _
& "Place: _ _ _ _ _ _ _ _ Name of Authorised Person: : _ _ _ _ _ _ _ _ _" & vbNewLine _
& vbNewLine _
& "Designation: _ _ _ _ _ _ _ _"


With outlookMailItem

.to = recipient
.CC = CC
.BCC = ""
.body = MailBody
.Subject = Customer & "-" & CustomerCode & " - Balance confirmation as on 31st March 2021."
.Display

End With

Set outlookMailItem = Nothing
Set outlookApp = Nothing


End Sub



Kindly help me in VBA.

Thanks in Advance.

Regards,
Kunal

 

[KOKOSEK]

To formating text in email you have to create email in HTML by HTMLBody.
Then you can use HTML tags - for bold <B>BOLD TEXT</B> and instead of vbNewLine use <BR> as new line.

 

[kunal86]

To formating text in email you have to create email in HTML by HTMLBody.
Then you can use HTML tags - for bold <B>BOLD TEXT</B> and instead of vbNewLine use <BR> as new line.

Thanks for the reply.

I have tried using HTMLBody also.
But getting error.

Can you please provide or edit VBA in attached sheet.

Thank you in advance.

 

[KOKOSEK]

This works for me

Sub CreateMail()
    Dim objWorksheet As Excel.Worksheet
    Dim objOutlookApp, objMail, objMailDocument As Object
    
    'Get the specific worksheet
    Set objWorksheet = ThisWorkbook.Worksheets(1)
    'Create a new email
    Set objOutlookApp = CreateObject("Outlook.application")
    Set objMail = objOutlookApp.CreateItem(objOutlookAppobjMailItem)


     With objMail
        .To = "XXXXYYYY@gmail.com"
        .CC = ""
        .BCC = ""
        .Subject = "Your email"
        .HTMLBody = "<html>" & _
                "<p style=""text-align:left"">Hi,</p>" & _
                "<p style=""text-align:left"">Some more text here.. </p>" & _
                "<p style=""text-align:left""><b>some more BOLD text </b></p>" & _
                "<p style=""text-align:left;color: #FF0000;"">some more red text</p>" & _
                "</html>"
        .display
    End With
End Sub