Pages

▼

Open an outlook email and populate the body of the email







             Today I'm going to explain you how to generate Outlook email and populate with the required details of a worksheet automatically using VBA.                                                          
Here below is a example worksheet. It contains ID1, Email Addess, First name and Data.
And there is a button call "E-mail" in that sheet. What we need to do is Once we click the button it should  ask us to enter row number of data we want to email. When we input that number and press OK, Message should be opened with required data.













So first we need to get the row number from the user.


Dim RowNo As Integer
RowNo = InputBox("Please enter the row number you need to email:", "")


So it will show this input box.











Then we need to create the body of the email.

Dim WS As Worksheet
Dim messageBody As String

Set WS = ActiveSheet

messageBody = "Dear " & WS.Range("C" & RowNo) & "," & vbCrLf & vbCrLf & _
"Your ID1: " & WS.Range("A" & RowNo) & vbCrLf & vbCrLf & _
"Your email address: " & WS.Range("B" & RowNo) & vbCrLf & vbCrLf & _
"Your data: " & WS.Range("D" & RowNo) & vbCrLf & vbCrLf & _
"Please store this information in a safe place." & vbCrLf & vbCrLf & _
"Kind regards," & vbCrLf & vbCrLf & _
"Paul"

So message will be constructed similar to below.

Finally we need to create the outlook mail. Below code will do that part.

Dim OutApp As Outlook.Application
Dim objMsg As MailItem

Set OutApp = CreateObject("Outlook.Application")
Set objMsg = OutApp.CreateItem(olMailItem)

With objMsg
  .Body = messageBody
  .Display
End With

And here is the full code for this automation.



Dim RowNo As Integer
RowNo = InputBox("Please enter the row number you need to email:", "")
Dim WS As Worksheet
Dim messageBody As String
Set WS = ActiveSheet
messageBody = "Dear " & WS.Range("C" & RowNo) & "," & vbCrLf & vbCrLf & _
"Your ID1: " & WS.Range("A" & RowNo) & vbCrLf & vbCrLf & _
"Your email address: " & WS.Range("B" & RowNo) & vbCrLf & vbCrLf & _
"Your data: " & WS.Range("D" & RowNo) & vbCrLf & vbCrLf & _
"Please store this information in a safe place." & vbCrLf & vbCrLf & _
"Kind regards," & vbCrLf & vbCrLf & _
"Paul"
Dim OutApp As Outlook.Application
Dim objMsg As MailItem
Set OutApp = CreateObject("Outlook.Application")
Set objMsg = OutApp.CreateItem(olMailItem)
With objMsg
  .Body = messageBody
  .Display
End With

SUMIF function

Today I will explain you how to use SUMIF function. It is much easier to explain this through an example. See below excel sheet.


Sample data for sumif function

This is a data set of a business. How it works is each product has its own row. If column B is empty that means they have not sold it yet. If column B has a value that means that they have sold that particular item.

So if we need to get Total Sales or Total Purchase we can use SUM function.

Think If we want to add up all  items that are unsold. That means if column B is empty, We need to  add up all the costs of those products, which is in column C.
So for example in row 5 there is a product call item 3. Cell B5 is blank. So we need to add the purchase price of that item in cell C5.
Row 9 also has same thing. B9 is blank so we need to add C5 with C9 and so on.

How do we do that?

We can use SUMIF function for situations like this. To calculate Unsold Inventory we can use below formula

==SUMIF(x,y,z)

x is the range we are looking.
y is the criteria we check
z is the range we sum

For above example the formula should be written as this

=SUMIF(B2:B20,"",C2:C20)

We check the range B2:B20 for empty cells. that means for "". If empty cell found we add the values in the range C2:C20.