Subscript out of range

Question

Subscript out of range

Saifuddin Mohammad 0


Sub SendEmail_Demo1()

Dim EmailApp As Outlook.Application
Set EmailApp = New Outlook.Application
Dim NewEmailItem As Outlook.MailItem
Set NewEmailItem = EmailApp.CreateItem(olMailItem)


Dim i As Integer
For i = 4 To 27

 NewEmailItem.To = Workbooks("CATS_Compliance(1).xlsm").Worksheets("TB_Time_HRS").Cells(i, 7).Value
 NewEmailItem.Subject = "Macro_TWR Compliance - Missing TWR Hours"
 With NewEmailItem
  
 .BodyFormat = olFormatHTML
 
 .HTMLBody = "<HTML><BODY>You have a missing time report. Would you please submit the missing time writing at the earliest. </BODY></HTML>"
 
 .Display
 
 .Send
           
 End With

Next

End Sub

ChristyZhang-MSFT 26,186 Reputation points Microsoft External Staff

2023-04-12T03:38:33.5466667+00:00

Hi @Saifuddin Mohammad ,

Welcome to our forum! Looks like your issue is more related to development, please kindly understand that the Outlook tag here we mainly focus on general issues about Outlook desktop client. In order to better solve your problem, we will add "Office Development" tag. Thanks for your understanding and hope your issue would be resolved soon.
Saifuddin Mohammad 0 Reputation points

2023-04-12T11:48:10.35+00:00

Thank you Christy for your comment. I am new to the forum and your comment helps me in my new queries for tag.
Viorel 125.7K Reputation points

2023-04-13T07:25:07.4566667+00:00

Maybe the name of workbook or worksheet is wrong?

Which line generates the error?
Saifuddin Mohammad 0 Reputation points

2023-04-13T17:20:53.93+00:00

names of the workbooks and sheet are correct... not sure which line is having error....

Saifuddin Mohammad 0

This is working for single item, without loop... but with loop it is not working:

Sub SendEmail_Demo()

Dim EmailApp As Outlook.Application
Set EmailApp = New Outlook.Application
Dim NewEmailItem As Outlook.MailItem
Set NewEmailItem = EmailApp.CreateItem(olMailItem)

NewEmailItem.To = Worksheets("TB_Time_HRS").Cells(4, 7).Value
NewEmailItem.Subject = "Macro_TWR Compliance - Missing TWR Hours i is working fine"

With NewEmailItem 
 .BodyFormat = olFormatHTML
 .HTMLBody = "<HTML><BODY>You have a missing time report. Would you please submit the missing time writing at the earliest. </BODY></HTML>"
 
 .Display
 
 .Send
      
End With

End Sub

1 answer

Your answer

ChristyZhang-MSFT 26,186 Reputation points Microsoft External Staff

2023-04-12T03:38:33.5466667+00:00

Hi @Saifuddin Mohammad ,

Welcome to our forum! Looks like your issue is more related to development, please kindly understand that the Outlook tag here we mainly focus on general issues about Outlook desktop client. In order to better solve your problem, we will add "Office Development" tag. Thanks for your understanding and hope your issue would be resolved soon.
Saifuddin Mohammad 0 Reputation points

2023-04-12T11:48:10.35+00:00

Thank you Christy for your comment. I am new to the forum and your comment helps me in my new queries for tag.
Viorel 125.7K Reputation points

2023-04-13T07:25:07.4566667+00:00

Maybe the name of workbook or worksheet is wrong?

Which line generates the error?
Saifuddin Mohammad 0 Reputation points

2023-04-13T17:20:53.93+00:00

names of the workbooks and sheet are correct... not sure which line is having error....
Saifuddin Mohammad 0 Reputation points

2023-04-13T17:25:20.7966667+00:00

This is working for single item, without loop... but with loop it is not working:

Sub SendEmail_Demo() Dim EmailApp As Outlook.Application Set EmailApp = New Outlook.Application Dim NewEmailItem As Outlook.MailItem Set NewEmailItem = EmailApp.CreateItem(olMailItem) NewEmailItem.To = Worksheets("TB_Time_HRS").Cells(4, 7).Value NewEmailItem.Subject = "Macro_TWR Compliance - Missing TWR Hours i is working fine" With NewEmailItem .BodyFormat = olFormatHTML .HTMLBody = "<HTML><BODY>You have a missing time report. Would you please submit the missing time writing at the earliest. </BODY></HTML>" .Display .Send End With End Sub

Answer 1

MotoX80 37,156

not sure which line is having error....

One thought is that the NewEmailItem object has been disposed of as part of the .Send call. Move the Set command inside of the loop and see if that works.

Dim i As Integer 
For i = 4 To 27
   Set NewEmailItem = EmailApp.CreateItem(olMailItem)

Add msgbox or debug.print statements to display what steps your code is doing and what variable contents are. You have to figure out what statement and variable are causing your problem because we don't have access to your spreadsheet.

Saifuddin Mohammad 0 Reputation points

2023-04-14T09:30:13.79+00:00

I have attached the pdf file (as excel is not allowed) and below is the data: I would macro to send mails to all the emails mentioned until the last row.
MotoX80 37,156 Reputation points

2023-04-14T11:36:52.54+00:00

https://www.bing.com/search?q=how+to+debug+excel+macro

Share via

Subscript out of range

1 answer

Your answer