Now when you click the button, it will open the Outlook message window, which will have all the emails ids in the 'To.' box, with the subject and body. In the above example, the variable that has the id’s are assigned to the. Using a For Each … loop, I’ll iterate through each row in the specified range to extract the email ids and store the values in a variable.įinally, send an email to each recipient’s id. What is a Named Range in Excel? Read these posts here and here. Set myDataRng = Range('B1:B10' & Cells(Rows.Count, 'B').End(xlUp).Row) Next, to get access to all the email ids from my worksheet, I’ll define a Range. Set objEmail = objOutlook.CreateItem(olMailItem) That's what I did in the beginning of the code. To get access to Microsoft Outlook methods and properties in VBA, you'll have to first create an instant of Outlook, using the CreateObject function. Set objEmail = Nothing: Set objOutlook = Nothing Display ' Display outlook message window. Subject = 'This is a test message from Arun Banik' to = sMail_ids ' Assign all email ids to the property.

Set myDataRng = Nothing ' Clear the range. SMail_ids = sMail_ids & vbCrLf & ' ' & cell.Offset(1, 0).Value ' Run a loop to extract email ids from the 2nd column. Set myDataRng = Range('B1:B10' & Cells(Rows.Count, 'B').End(xlUp).Row)ĭim iCnt As Integer ' Its just a counter.ĭim sMail_ids As String ' To store recipients email ids. Set objOutlook = CreateObject('Outlook.Application')

How to add from field in outlook