Link to home
Start Free TrialLog in
Avatar of Aparna Gannavarapu
Aparna Gannavarapu

asked on

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
Avatar of Norie
Norie

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


»bp
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
In Visual Basic, in Tools->References, do you have the Microsoft Office Outlook 14.0 Object Library selected?  (or any other version).
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
it has object library and the follow up sheet as well.
error is subscript out of range
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
Avatar of Aparna Gannavarapu

ASKER

Still have not got any solution for this, I am not really sure what is going wrong in this.
Best approach might be to post you workbook at this point so we can trouble shoot it.  


»bp
And if the workbook contains sensitive data then please post a small sample workbook containing  non-sensitive data.
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
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
I also didn't find "sendmail" in any cells of that sheet...


»bp
so first i would remove ' this before x to set it as variable ,

Send email is in the y and z columns.
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
even though the formulas are linked to today's date. It still has been sending the previous mail.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial