Send Ledger Confirmation mail from Excel

kunal86

New Member
Joined
Apr 20, 2015
Messages
3
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

KOKOSEK

Active Member
Joined
Apr 8, 2019
Messages
270
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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

New Member
Joined
Apr 20, 2015
Messages
3
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

Active Member
Joined
Apr 8, 2019
Messages
270
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
This works for me

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,912
Messages
5,655,912
Members
418,250
Latest member
Jebacmakro

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
Top