Error - auto mail extracting mail address from cells

Mahesh Rana

New Member
Joined
Aug 2, 2018
Messages
6
Need your support
Thus code send mail to all the addresses in Column A as .TO, B as .CC & C as .BCC BUT
for each no. of mail-ids, I had to click [allow] as many times in outlook to send mail.
Would it be possible to click [allow] only once to send mail.

Code:
Option Explicit


Private Sub CommandButton1_Click()
On Error GoTo ErrHandler
Dim lrow As Long 'last row


With ActiveSheet
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With


' SET Outlook APPLICATION OBJECT.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")


' CREATE EMAIL OBJECT.
Dim objEmail As Object
Dim I As Integer


For I = 2 To lrow


    Set objEmail = objOutlook.CreateItem(0)
    
    On Error Resume Next
    With objEmail
        .To = Range("A" & I)
        .CC = Range("B" & I)
        .BCC = Range("C" & I)
        .Subject = "COLD CHAIN DISPATCHES DATED " & Date
        .Body = "Pls. have the details in attachment."
        .Attachments.Add ("C:\Users\NARESH\Desktop\COLD CHAIN RECIPT.xlsx")
        .Send
    End With


Next I


' CLEAR.
Set objEmail = Nothing: Set objOutlook = Nothing


ErrHandler:
'
End Sub
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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
Back
Top