Need help with if then else in VBA

[Barry]

I am trying to generate a message box if the value of Document Link is null
and create an E-Mail if it is not null. The message box appears if the value
is null but the email also appears.

How do I just get the mesage box when the value is null.

Thanks in advance for your help.

Code is below.

Private Sub Text199_Change()
Dim TRACKING As Variant
Dim EMAILADD As Variant
Dim DOCLINK As Variant

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

If IsNull(Me![Document Link]) Then
MsgBox "Document Link is not entered!"

Else


TRACKING = Me![ARL TRACKING NO]
EMAILADD = Me!EMAIL
DOCLINK = Me![Document Link]

End If

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = EMAILADD
.CC = "(e-mail address removed)"
.Subject = "Service Contract ARL Tracking NO " & TRACKING
.ReadReceiptRequested = False
.Body = "Mr. Miller has approved ARL Tracking No " & TRACKING & ".
You may view the approval by clicking on the document link in the Service
Contracts Data Base or click on the following link." & "<file:\\" & DOCLINK &
">"
.Body = "Mr. Miller has approved ARL Tracking No " & TRACKING & ".
You may view the approval by clicking on the document link in the Service
Contracts Data Base or click on the following link." & "<file:\\" &
Replace(DOCLINK, "#", "") & ">"

.Display

End With
End Sub

 

[Jack Leach]

Everything after your End If statement is going to run regardless of your
condition. I would enclose this all into the If/Then statement.

....
....

If Not IsNull(Me![Document Link]) Then
With objEmail
.To = EMAILADD
....
....
.Display
End With
Else
Msgbox "message"
End If

End Sub

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Barry]

Thanks it worked like a charm!

Everything after your End If statement is going to run regardless of your
condition. I would enclose this all into the If/Then statement.

...
...

If Not IsNull(Me![Document Link]) Then
With objEmail
.To = EMAILADD
....
....
.Display
End With
Else
Msgbox "message"
End If

End Sub

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

I am trying to generate a message box if the value of Document Link is null
and create an E-Mail if it is not null. The message box appears if the value
is null but the email also appears.

How do I just get the mesage box when the value is null.

Thanks in advance for your help.

Code is below.

Private Sub Text199_Change()
Dim TRACKING As Variant
Dim EMAILADD As Variant
Dim DOCLINK As Variant

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

If IsNull(Me![Document Link]) Then
MsgBox "Document Link is not entered!"

Else


TRACKING = Me![ARL TRACKING NO]
EMAILADD = Me!EMAIL
DOCLINK = Me![Document Link]

End If

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = EMAILADD
.CC = "(e-mail address removed)"
.Subject = "Service Contract ARL Tracking NO " & TRACKING
.ReadReceiptRequested = False
.Body = "Mr. Miller has approved ARL Tracking No " & TRACKING & ".
You may view the approval by clicking on the document link in the Service
Contracts Data Base or click on the following link." & "<file:\\" & DOCLINK &
">"
.Body = "Mr. Miller has approved ARL Tracking No " & TRACKING & ".
You may view the approval by clicking on the document link in the Service
Contracts Data Base or click on the following link." & "<file:\\" &
Replace(DOCLINK, "#", "") & ">"

.Display

End With
End Sub