ERROR IN VBA CODE

Sub emailing()
'
' emailing Macro
'
' Keyboard Shortcut: Ctrl+g
'
  Dim x As String
Dim icolumn As Integer
Dim jrow As Integer
Dim emal As String

    ' SET Outlook APPLICATION OBJECT.
    Dim objOutlook As Object
   
    ' CREATE EMAIL OBJECT.
    Dim objEmail As Object
   

For jrow = 4 To 10000
For icolumn = 19 To 22

'x = Worksheets("Followupsheet").Cells(jrow, icolumn + 5).Value
Worksheets("Followupsheet").Cells(4, 24).Value = jrow


If x = "sendemail" Then
Set objOutlook = CreateObject("Outlook.Application")
    Set objEmail = objOutlook.CreateItem(olMailItem)
    With objEmail
    'email to column 21 in the respective row i.e. column u
        .To = Worksheets("Followupsheet").Cells(jrow, 28).Value
    'cc to column 22 in the respective row i.e. column v
        .Cc = Worksheets("Followupsheet").Cells(jrow, 29).Value
       
        .Subject = "Escalation email Order # " & Worksheets("Followupsheet").Cells(jrow, 4).Value & " - " & Worksheets("Followupsheet").Cells(jrow, 3).Value
        .Body = Worksheets("Followupsheet").Cells(jrow, icolumn).Value
        .SEND
    End With
   
    ' CLEAR.
Set objEmail = Nothing:    Set objOutlook = Nothing


Else
End If



    Next icolumn
   
    Next jrow



 
   
End Sub


I have written this code to send emails, but there is some issue because of which the emails are not been sent when we try to run the code. Debugger highlights the above bold lines.  CAN YOU TELL IF THERE IS ANY SYNTAX ERROR OR REFERENCE ERROR
Aparna GannavarapuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
The syntax looks fine, what's the error message?
0
Bill PrewCommented:
Does a sheet named "Followupsheet" actually exist in the current active workbook?


»bp
0
Bill PrewCommented:
In addition, as it stands, you check the following to determine if you will send an email, but x is not being set currently so will never be true.

If x = "sendemail" Then


»bp
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Martin LissOlder than dirtCommented:
In Visual Basic, in Tools->References, do you have the Microsoft Office Outlook 14.0 Object Library selected?  (or any other version).
0
Bill PrewCommented:
Martin,

This code looks like it is doing Late Binding, so I don't think the Reference is needed in the IDE...

However since at least one Outlook constant (olMailItem) is being used, then if the Reference is not set up, it would need to be defined in this code.  However it has a value of 0, so the code would likely work as is...


»bp
0
Appy gCommented:
it has object library and the follow up sheet as well.
0
Appy gCommented:
error is subscript out of range
0
Bill PrewCommented:
Subscript out of range on that statement indicates it can't find that named sheet, double check the sheet names, perhaps there is a typo or a space that is not matching.


»bp
0
Aparna GannavarapuAuthor Commented:
Still have not got any solution for this, I am not really sure what is going wrong in this.
0
Bill PrewCommented:
Best approach might be to post you workbook at this point so we can trouble shoot it.  


»bp
0
Martin LissOlder than dirtCommented:
And if the workbook contains sensitive data then please post a small sample workbook containing  non-sensitive data.
0
Aparna GannavarapuAuthor Commented:
this is the excel file , which sends emails. my previous manager has made this and  since past few days this is not running when we  are trying to run the macros. The mails are not being delivered.
Tracker.xlsm
0
Bill PrewCommented:
The code did not error for me, it ran as it should have.  However, given the following line:

'x = Worksheets("Followupsheet").Cells(jrow, icolumn + 5).Value
Worksheets("Followupsheet").Cells(4, 24).Value = jrow

The x variable is never being set, so below that when doing:

If x = "sendemail" Then

That condition will never be true, so no mail would ever be sent.


»bp
0
Bill PrewCommented:
I also didn't find "sendmail" in any cells of that sheet...


»bp
0
Aparna GannavarapuAuthor Commented:
so first i would remove ' this before x to set it as variable ,

Send email is in the y and z columns.
0
Bill PrewCommented:
Okay, I made a few adjustments and this seems to be working okay here now.  See if it works better there.

Sub emailing()
    '
    ' emailing Macro
    '
    ' Keyboard Shortcut: Ctrl+g
    '
    Dim x As String
    Dim icolumn As Integer
    Dim jrow As Integer
    Dim emal As String

    ' SET Outlook APPLICATION OBJECT.
    Dim objOutlook As Object
        
    ' CREATE EMAIL OBJECT.
    Dim objEmail As Object
        
    ' Look at all used rows of sheet (based on column A)
    For jrow = 4 To ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp)
    ' Checking for "sendemail" in columns Y and Z
        For icolumn = 25 To 26

        x = Worksheets("Followupsheet").Cells(jrow, icolumn).Value
        Worksheets("Followupsheet").Cells(4, 24).Value = jrow

        If x = "sendemail" Then
            Set objOutlook = CreateObject("Outlook.Application")
            Set objEmail = objOutlook.CreateItem(olMailItem)
            With objEmail
                'email to column 21 in the respective row i.e. column u
                .To = Worksheets("Followupsheet").Cells(jrow, 28).Value
                'cc to column 22 in the respective row i.e. column v
                .Cc = Worksheets("Followupsheet").Cells(jrow, 29).Value
                .Subject = "Escalation email Order # " & Worksheets("Followupsheet").Cells(jrow, 4).Value & " - " & Worksheets("Followupsheet").Cells(jrow, 3).Value
                .Body = Worksheets("Followupsheet").Cells(jrow, icolumn - 5).Value
                .Send
            End With
            
            ' CLEAR.
            Set objEmail = Nothing
            Set objOutlook = Nothing
        End If

        Next icolumn
    
    Next jrow
        
End Sub

Open in new window


»bp
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aparna GannavarapuAuthor Commented:
the code is Running , But i have one question,

We will need to run within certain intervals, so will the previous emails also be sent again and again every time we run? Because when i tried it now, older mails have been sent, but next time when i run , i don't want the 2017 emails to be running, is there a solution for this?
0
Aparna GannavarapuAuthor Commented:
even though the formulas are linked to today's date. It still has been sending the previous mail.
0
Bill PrewCommented:
If you are talking about these formulas:

=IF(L4="","-",IF(AND(TODAY()>($L4+5),$Q4=""),"sendemail","-"))

Then it makes sense the way they are written that once

TODAY()>($L4+5)

Then for future days that will also be true and another email will be sent.

You might want to adjust that formula if possible, although specifying

TODAY()=($L4+5)

feels a little risky, if the file isn't run on every day.

Perhaps you want to either update that column when you send the email to then have a value of "sent"?  

Or add additional flag columns that you set when the email is sent, and in the VBA procedure check those as well and only send once, when they are blank?


»bp
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Outlook

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.