Do While code error

[Jingles3X]

Hello! I am new to vba (learning fairly quickly i think),am stuck and am hoping someone can help me. I keep getting an 'Object Required' error and I am not sure why/what I am missing.
I don't think it's anything major, but it's screwing me up and I am getting frustrated.
The code is:

Sub Email_123()
Dim i As Integer
Dim w As Workbook
Dim ws As Worksheet
Dim xOTApp As Object
Dim myattachments As Object

 With CreateObject("Outlook.Application").CreateItem(0)

'Do While Not sContacts.Cells(i, column).Value = ""
             '  xEmailAddr = sContacts.Cells(i, column).Value & ";" & xEmailAddr
             '  i = i + 1
   ' Loop
   ' Set i = xOTApp.CreateItem(0)
    .To = " "
    .CC = " "
    .BCC = Range("B4").Value
    .Subject = Range("D4").Value
    .body = Range("B11")
    .attachments.Add Range("b7").Value
    .Display
  
 End With
End Sub

 

[Joe4]

Do you mean you get the error when the Loop is uncommented?
For one thing, it looks like you are trying to reference an object named "sContacts", but I do not that being defined anywhere in your code.

 

[Jingles3X]

The error i seem to be receiving is "Object required" with the Title of the sub highlighted yellow, and further down, the " Set i = " statement with I= in blue.

Sub Email_123()
Dim i As Integer
Dim w As Workbook
Dim ws As Worksheet
Dim xOTApp As Object


 With CreateObject("Outlook.Application").CreateItem(0)

Do While Not ws("Contacts").Cells(i, column).Value = ""
               xEmailAddr = wsContacts.Cells(i, column).Value & ";" & xEmailAddr
               i = i + 1
    Loop
     Set i = xOTApp.CreateItem(0)
    .To = " "
    .CC = " "
    .BCC = Range("B4").Value
    .Subject = Range("D4").Value
    .body = Range("B11")
    .attachments.Add Range("b7").Value
    .Display
    
 End With
End Sub

 

[Joe4]

You have declared "i" to be an Integer here:

Dim i As Integer

You do NOT use the "Set" command with numbers, only objects.

So this line of code is valid:

i = i + 1

but this one is not:

Set i = xOTApp.CreateItem(0)

as you cannot use "Set" with numbers.

 

[KenU]

You have several undeclared variables and VBA will not know what to do with those. When you declare objects such as the worksheet object, they need to be "Set" to the appropriate reference. Since I don't know exactly what you are trying to accomplish or the structure of your file, I have stripped out the parts that are not related to my corrections. There may still be issues with the Outlook part that I haven't addressed.

Dim iRow As Integer
Dim iCol As Integer
Dim ws As Worksheet

Set ws = Worksheets("Contacts")
iRow = 4
iCol = 1    'this is column A

    Do While Not ws.Cells(iRow, iCol).Value = ""
        xEmailAddr = wsContacts.Cells(iRow, iCol).Value & ";" & xEmailAddr
        iRow = iRow + 1
    Loop

Hope that helps.

Regards,
Ken

 

[KenU]

Please see the code below. This was something I wrote for a colleague a few years ago. It creates and sends email from Outlook. The sample used only three columns: Email address, Subject, and Body text. It could be expanded by uncommenting the placeholders in code and editing to direct the code to the correct columns.

Sub SendOutlookMail()

Dim rTo As Range
Dim strTo As String, strCc As String, strBcc As String, strSubject As String, strBody As String
Dim OutApp As Object
Dim OutMail As Object

Application.ScreenUpdating = False

Set OutApp = CreateObject("Outlook.Application")

Set rTo = Worksheets("Sheet1").Range("A2")

Do While Not IsEmpty(rTo.Value)
    Set OutMail = OutApp.CreateItem(0)
    strTo = rTo.Value
    'strCc = ""
    'strBcc = ""
    strSubject = rTo.Cells(1, 2)
    strBody = rTo.Cells(1, 3)
    
    With OutMail
        .Subject = strSubject
        .To = strTo
        '.cc = strCc
        '.BCC = strBcc
        .Body = strBody
        .send
    End With
    Set rTo = rTo.Cells(2, 1)
Loop

MsgBox "Your message has been sent."

Application.ScreenUpdating = True

End Sub

Hope that helps.

Regards,
Ken

 

[Jingles3X]

Please see the code below. This was something I wrote for a colleague a few years ago. It creates and sends email from Outlook. The sample used only three columns: Email address, Subject, and Body text. It could be expanded by uncommenting the placeholders in code and editing to direct the code to the correct columns.

Sub SendOutlookMail()

Dim rTo As Range
Dim strTo As String, strCc As String, strBcc As String, strSubject As String, strBody As String
Dim OutApp As Object
Dim OutMail As Object

Application.ScreenUpdating = False

Set OutApp = CreateObject("Outlook.Application")

Set rTo = Worksheets("Sheet1").Range("A2")

Do While Not IsEmpty(rTo.Value)
    Set OutMail = OutApp.CreateItem(0)
    strTo = rTo.Value
    'strCc = ""
    'strBcc = ""
    strSubject = rTo.Cells(1, 2)
    strBody = rTo.Cells(1, 3)
   
    With OutMail
        .Subject = strSubject
        .To = strTo
        '.cc = strCc
        '.BCC = strBcc
        .Body = strBody
        .send
    End With
    Set rTo = rTo.Cells(2, 1)
Loop

MsgBox "Your message has been sent."

Application.ScreenUpdating = True

End Sub

Hope that helps.

Regards,
Ken

Thank you Ken. What I am hoping to accomplish with this is to be able to keep multiple email lists (one per cell) in multiple columns. For example Column A Might be 1st Shift, Column B might be 2nd Shift, and so on. I would like to have sheet 1 be where i enter the subject, body, and filepath of the attachment, and sheet 2 to be where the email lists are kept. I will play around with your code, as it does seem to be a bit easier to read. Much appreciated.