How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask NewITZone Your Own Question

NewITZone
NewITZone, Computer Enthusiast
Category: Microsoft Office
Satisfied Customers: 90
Experience:  I have been using Office Pro for 10 years and implemented applications for business use.
53509759
Type Your Microsoft Office Question Here...
NewITZone is online now
A new question is answered every 9 seconds

I'm trying to write an excel VBA code to send emails to

Customer Question

I'm trying to write an excel VBA code to send emails to recipients in worksheet, and include links. I tried a VBA code just to test the email with sample data, and I keep getting End If without Block If error code. I have tried everything. Any suggestions?
Dim oOL As Outlook.Application, oMail As Outlook.MailItem, oNS As Outlook.Namespace
Dim oMapi As Outlook.MAPIFolder, oExpl As Outlook.Explorer
Dim sBody As String, sRecip As String, sSubj As String, sReady As String, bSend As Boolean
Dim oWS As Worksheet, r As Long, i As Long, sStart As String
If MsgBox("Send directly(Y) or display(N)?", vbYesNo) = vbYes Then bSend = True
End If
Set oWS = SharePoint
Set oOL = New Outlook.Application
Set oExpl = oOL.ActiveExplorer
If TypeName(oExpl) = "Nothing" Then
Set oNS = oOL.GetNamespace("MAPI")
Set oMapi = oNS.GetDefaultFolder(olFolderInbox)
Set oExpl = oMapi.GetExplorer
End If
With oWS.Range("A2")
r = .CurrentRegion.Rows.Count
For i = 3 To r
sReady = .Cells(i, 30)
'if sReady = True Then
sRecip = .Cells(i, 29)
sSubj = "LPI Weekly Trigger Attestation"
sBody = "RESPONSE REQUIRED BY WEDNESDAY OF EACH WEEK."
If bSend = False Then
Set oMail = oOL.CreateItem(olMailItem)
With oMail
.Subject = sSubj
.Body = sBody
.Recipients.Add sRecip
.Display 'NOT.Send
End If
End With 'SharePoint
Next i
Set oDL = Nothing
End Sub
Submitted: 3 months ago.
Category: Microsoft Office
Customer: replied 3 months ago.
This vba does not include adding a link to the body message, but I need to add a link and extend message of 250+ words
Expert:  Russell H. replied 3 months ago.

Hi, thank you for contacting JustAnswer.com. My name is Russell. I will do my best to provide the right answer to your question.

I think that this line:

If MsgBox("Send directly(Y) or display(N)?", vbYesNo) = vbYes Then

is defective owing to the right-hand parenthesis being too early in the line. Something like

If MsgBox("Send directly(Y) or display(N)?", vbYesNo = vbYes ) Then

very approximately, may make the subsequent Endif functional. Try something like that (as your programmer's good sense may indicate) and see if it works OK! let me know, by

Reply To Expert

and if needed I will advise you further.

Customer: replied 3 months ago.
I tried with your request and with the code below, and got the same error.
If MsgBox("Send directly(Y) or display(N)?", vbYesNo = vbYes) Then bSend = True.How do I include in the msg to auto send if vbYes = True and not get the End If error?
Expert:  Russell H. replied 3 months ago.

I've changed my mind on that.

If MsgBox("Send directly(Y) or display(N)?", vbYesNo) = vbYes Then

is probably OK. It's another IF block that is the problem.

Is there any number with that error code, or other line indication in the error?

Expert:  Russell H. replied 3 months ago.

Should

'if sReady = True Then

have a ' in front of it?

Customer: replied 3 months ago.
Now the line indicator is the End If after the .Display
Customer: replied 3 months ago.
I removed the ' in front of 'if sReady = True Then and got the same error.
Expert:  Russell H. replied 3 months ago.

Do you know what the

'

at the head of that line is doing?

And why that line's "IF" is spelled with a small 'i' ?

And yes, that's roughly where the problem is. You have a big block of code there, with two IFs, but only one End If. End with 'SharePoint has another

'

in it, and was that intentional? and is

End with Sharepoint

supposed to take the place of an

End If

that definitely isn't there ?

Customer: replied 3 months ago.
the ' is suppose to provide instructions on the VBA to say if the value in that range is True, then complete the next steps that following. I made the "If" in that column If and got same error message. I have tried closing all If statements with End If and continue to get the same error.
Expert:  Russell H. replied 3 months ago.

Something about that code block and the way you are using If

or

'If

is somehow incorrect.

I suspect that you should go back to your original code as stated to me at the beginning, then concentrate on getting that block beginning with

'if .........

and ending with

End With 'Sharepoint

and refine esp. those two beginning and ending statements to where they actually work as the book says they should, not just more-or-less. That's where the problem is for sure, anyway.

Let me know what progress you make, please.