0

I am trying to create email draft using excel data and add recipient into BCC, I am able to create Draft using below code but however if am trying to send email from Outlook draft folder I am getting an error as operation failed.

I am not sure if outlook format is not correct or any process I am missing.

Sub SendMultipleEmails()

Dim Mail_Object, OutApp As Object
Dim ws As Worksheet:
Dim arr() As Variant
Dim Pth As String
Dim file_name As String
Dim Month As String


Sheets("Report").UsedRange.ClearContents
Month = Sheets("Macro").Range("C5")
file_name = Sheets("Macro").Range("C4")
Pth = Sheets("Macro").Range("C3").Value
Sheets("Data").Select
Set ws = ActiveSheet
LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=ws.Range("I2:I" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.Sort
    .SetRange ws.UsedRange
    .Header = False
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

arr = ws.Range("I2:I" & LastRow)

Set Mail_Object = CreateObject("Outlook.Application")

first = 2

For i = LBound(arr) To UBound(arr)


    If i = UBound(arr) Then GoTo YO
    If arr(i + 1, 1) = arr(i, 1) Then
                first = WorksheetFunction.Min(first, i + 1)
    Else
YO:
    Set OutApp = Mail_Object.CreateItem(0)

    With OutApp
         .Subject = "My Acc Holding Holding")
         .Body = "Hello" & vbNewLine _
                & vbNewLine _
                & "Please find the attached Acc Holding"

         .Display
         bc = ws.Range("F" & i + 1).Value
     For j = first To i
        bc = bc & ";" & ws.Range("F" & j).Value
     Next
     .BCC = bc
     first = i + 2

      .Save


         first = i + 2

    End With
    End If

Next



End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
khyati dedhia
  • 81
  • 8
  • 1
    I **highly** recommend to use `Option Explicit` at the beginning of your code module, type declare all variables, indent code properly, and functionalize (I know, it's not a word) all your code so that each part (or procedure in some cases) does a very specific thing. This code is extremely hard to read because it's kind of like spaghetti code. Lastly, see the reason I voted to close the question to understand how to get better help with this question. – Scott Holtzman Sep 13 '19 at 15:48
  • The thing is that macro is working fine and able to save draft in draft folder but when I tried to send email from folder I am getting operation failed error, so I am not sure it's receipt format issue or something else. – khyati dedhia Sep 13 '19 at 15:52
  • 1
    There is a comment "I can only display emails automatically, but cannot send them through code. Cyber-Security team will not allow it." https://stackoverflow.com/questions/48104512/how-to-send-mail-when-the-send-does-not-work. If this is the case for you, to risk breaking the rules see https://stackoverflow.com/questions/17883088/sending-mail-using-outlook-where-the-send-method-fails – niton Sep 14 '19 at 18:27

0 Answers0