if no email address iferror end or skip

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
392
Office Version
  1. 365
Platform
  1. Windows
hi
this part of my code sends a message once a column is changed.
how and where do i edit my code to iferror or on error exit
if there is no email address listed in (Cells(Target.Row, "Z").Value) or (Cells(Target.Row, "AA").Value)

VBA Code:
If result = vbOK Then


            Set OutlookApp = CreateObject("Outlook.Application")
            Set OlObjects = OutlookApp.GetNamespace("MAPI")
            Set newmsg = OutlookApp.CreateItem(olMailItem)

            newmsg.Recipients.Add (Cells(Target.Row, "Z").Value) ' Add Recipients
            newmsg.Recipients.Add (Cells(Target.Row, "AA").Value)
            newmsg.Subject = Cells(Target.Row, "B").Value & "   Reimbursement"  ' Add Subject
            newmsg.Body = "This is to inform you that payment has been processed   "     ' Email Body
            newmsg.Display   'Display Email
            newmsg.Send      'Send Email
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
i would step over the whole thing if you test for the cell presence, probbaly only need to look at Z
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
392
Office Version
  1. 365
Platform
  1. Windows
what does that mean and how would i look at the code
at the moment if either z or aa are blank the code gives an error message
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
392
Office Version
  1. 365
Platform
  1. Windows
what code would i use for iferror
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

something like the first and last statements, the last being after anything that might have been to do with sending


VBA Code:
If  (Cells(Target.Row, "Z").Value) = "" Then GoTo NoEmail
If result = vbOK Then


            Set OutlookApp = CreateObject("Outlook.Application")
            Set OlObjects = OutlookApp.GetNamespace("MAPI")
            Set newmsg = OutlookApp.CreateItem(olMailItem)

            newmsg.Recipients.Add (Cells(Target.Row, "Z").Value) ' Add Recipients
            newmsg.Recipients.Add (Cells(Target.Row, "AA").Value)
            newmsg.Subject = Cells(Target.Row, "B").Value & "   Reimbursement"  ' Add Subject
            newmsg.Body = "This is to inform you that payment has been processed   "     ' Email Body
            newmsg.Display   'Display Email
            newmsg.Send      'Send Email

NoEmail:

your continued events
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
392
Office Version
  1. 365
Platform
  1. Windows
i put
on error resume next
before
newmsg.Recipients.Add (Cells(Target.Row, "Z").Value) ' Add Recipients
newmsg.Recipients.Add (Cells(Target.Row, "AA").Value)
newmsg.Subject = Cells(Target.Row, "B").Value & " Reimbursement" ' Add Subject
newmsg.Body = "This is to inform you that payment has been processed " ' Email Body
newmsg.Display 'Display Email
newmsg.Send 'Send Email

this seems to work. would this be a correct way to do things?
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
tht makes it move to the next statement, does it reliably produce an output
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
392
Office Version
  1. 365
Platform
  1. Windows
it seems to work for me as before the email sending it sees no email address and code completes
 

Watch MrExcel Video

Forum statistics

Threads
1,127,554
Messages
5,625,474
Members
416,109
Latest member
TripleA00123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top