rutlandict
asked on
MsgBox before sending email
Hello Experts,
Currently, I have a button where once it clicked, it will automatically send an email without opening the outlook. Some of the information will be extract from the form such as customer email, name and etc.
Below is part of my code:
Set OutApp = CreateObject("Outlook.Appl ication")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = Me.Email
.CC = ""
What I am trying to do now is, to set up a MsgBox when any of the data is not entered, such as if the email or customer name has blank records, then a MsgBox will appear.
Thanks!
Currently, I have a button where once it clicked, it will automatically send an email without opening the outlook. Some of the information will be extract from the form such as customer email, name and etc.
Below is part of my code:
Set OutApp = CreateObject("Outlook.Appl
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = Me.Email
.CC = ""
What I am trying to do now is, to set up a MsgBox when any of the data is not entered, such as if the email or customer name has blank records, then a MsgBox will appear.
Thanks!
Besides the messagebox, what do you want to do?
I would do this before the line starting: Set OutApp
Dim strErrMsg as string
If me.email = "" Then strErrMsg = "Email address missing!
if me.Subject = "" then strErrMsg = (strErrMsg + vbcrlf) & "Subject missing!"
if me.Body = "" then strErrMsg = (strErrMsg + vbcrlf) & "Message body missing!"
If Len(strErrMsg) > 0 Then
msgbox strErrMsg, vbOKOnly
Exit Sub
End If
I would do this before the line starting: Set OutApp
Dim strErrMsg as string
If me.email = "" Then strErrMsg = "Email address missing!
if me.Subject = "" then strErrMsg = (strErrMsg + vbcrlf) & "Subject missing!"
if me.Body = "" then strErrMsg = (strErrMsg + vbcrlf) & "Message body missing!"
If Len(strErrMsg) > 0 Then
msgbox strErrMsg, vbOKOnly
Exit Sub
End If
ASKER
Hey guys, thanks for the fast reply.
Have tried both of the codes and the email still sent without any warning.
Here's my code:
Dim strErrMsg As String
If Me.Email = "" Then strErrMsg = "Email address missing!"
If Me.Extension = "" Then strErrMsg = (strErrMsg + vbCrLf) & "Extension Number missing!"
If Me.VoicemailPin = "" Then strErrMsg = (strErrMsg + vbCrLf) & "Voicemail Pin missing!"
If Len(strErrMsg) > 0 Then
MsgBox strErrMsg, vbOKOnly
Exit Sub
End If
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Appl ication")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = Me.Email
.CC = ""
.BCC = """"
.Subject = "Test"
.HTMLBody = "<HTML>Dear " & Me.ClientName & "<BR>" _
.Send
MsgBox ("Email has been sent successfully")
.Sent
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Have also tested Capricorn's code before .Send
Thanks
Have tried both of the codes and the email still sent without any warning.
Here's my code:
Dim strErrMsg As String
If Me.Email = "" Then strErrMsg = "Email address missing!"
If Me.Extension = "" Then strErrMsg = (strErrMsg + vbCrLf) & "Extension Number missing!"
If Me.VoicemailPin = "" Then strErrMsg = (strErrMsg + vbCrLf) & "Voicemail Pin missing!"
If Len(strErrMsg) > 0 Then
MsgBox strErrMsg, vbOKOnly
Exit Sub
End If
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Appl
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = Me.Email
.CC = ""
.BCC = """"
.Subject = "Test"
.HTMLBody = "<HTML>Dear " & Me.ClientName & "<BR>" _
.Send
MsgBox ("Email has been sent successfully")
.Sent
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Have also tested Capricorn's code before .Send
Thanks
<Have also tested Capricorn's code before .Send>
post the codes that you used , the whole sub..
post the codes that you used , the whole sub..
ASKER
Hello Capricorn, here's the code:
Private Sub Command550_Click()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Appl ication")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Me.Email
.CC = ""
.BCC = "i_am@alvinluo.me"""
.Subject = "Test"
.HTMLBody = "<HTML>Dear " & Me.ClientName & "<BR>" _
Dim sVar(), j
sVar = Array("sTo", "sCC", "sClientName")
For j = LBound(sVar) To UBound(sVar)
If Len(Trim(sVar(j))) = 0 Then
MsgBox "Please enter information for " & sVar(j)
End If
Next
.Send
MsgBox ("Email has been sent successfully")
.Sent
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Thanks!
Private Sub Command550_Click()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Appl
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = Me.Email
.CC = ""
.BCC = "i_am@alvinluo.me"""
.Subject = "Test"
.HTMLBody = "<HTML>Dear " & Me.ClientName & "<BR>" _
Dim sVar(), j
sVar = Array("sTo", "sCC", "sClientName")
For j = LBound(sVar) To UBound(sVar)
If Len(Trim(sVar(j))) = 0 Then
MsgBox "Please enter information for " & sVar(j)
End If
Next
.Send
MsgBox ("Email has been sent successfully")
.Sent
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Thanks!
where are your variables
"sTo", "sCC", "sClientName"
test this one
Private Sub Command550_Click()
dim sTo, sCC, sClientName
Dim OutApp As Object
Dim OutMail As Object
sTo=Me.Email
sCC="whatever"
sClientName=Me.ClientName
Set OutApp = CreateObject("Outlook.Appl ication")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = sto
.CC = ""
.BCC = "i_am@alvinluo.me"""
.Subject = "Test"
.HTMLBody = "<HTML>Dear " & sClientName & "<BR>" _
Dim sVar(), j
sVar = Array("sTo", "sCC", "sClientName")
For j = LBound(sVar) To UBound(sVar)
If Len(Trim(sVar(j))) = 0 Then
MsgBox "Please enter information for " & sVar(j)
exit sub
End If
Next
.Send
MsgBox ("Email has been sent successfully")
.Sent
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
"sTo", "sCC", "sClientName"
test this one
Private Sub Command550_Click()
dim sTo, sCC, sClientName
Dim OutApp As Object
Dim OutMail As Object
sTo=Me.Email
sCC="whatever"
sClientName=Me.ClientName
Set OutApp = CreateObject("Outlook.Appl
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = sto
.CC = ""
.BCC = "i_am@alvinluo.me"""
.Subject = "Test"
.HTMLBody = "<HTML>Dear " & sClientName & "<BR>" _
Dim sVar(), j
sVar = Array("sTo", "sCC", "sClientName")
For j = LBound(sVar) To UBound(sVar)
If Len(Trim(sVar(j))) = 0 Then
MsgBox "Please enter information for " & sVar(j)
exit sub
End If
Next
.Send
MsgBox ("Email has been sent successfully")
.Sent
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
better
Private Sub Command550_Click()
dim sTo, sCC, sClientName
Dim OutApp As Object
Dim OutMail As Object
sTo=Me.Email
sCC="whatever"
sClientName=Me.ClientName
Dim sVar(), j
sVar = Array("sTo", "sCC", "sClientName")
For j = LBound(sVar) To UBound(sVar)
If Len(Trim(sVar(j))) = 0 Then
MsgBox "Please enter information for " & sVar(j)
exit sub
End If
Next
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = sto
.CC = ""
.BCC = "i_am@alvinluo.me"""
.Subject = "Test"
.HTMLBody = "<HTML>Dear " & sClientName & "<BR>" _
.Send
MsgBox ("Email has been sent successfully")
.Sent
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
ASKER
Hi Capricorn, tested your better script and did not work either.
here's my code:
Private Sub Command550_Click()
Dim sTo, sTel, sClientName
Dim OutApp As Object
Dim OutMail As Object
sTo = Me.Email
sTel = Me.Tel
sClientName = Me.ClientName
Dim sVar(), j
sVar = Array("sTo", "sClientName", "sTell")
For j = LBound(sVar) To UBound(sVar)
If Len(Trim(sVar(j))) = 0 Then
MsgBox "Please enter information for " & sVar(j)
Exit Sub
End If
Next
Set OutApp = CreateObject("Outlook.Appl ication")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "Me.Email"
.CC = ""
.BCC = """"
.Subject = "Test"
.HTMLBody = "<HTML>Dear " & Me.ClientName & "<BR>" _
.Send
MsgBox ("Email has been sent successfully")
.Sent
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Inside my form has a tab control, the textboxes are in Tab1 and my button is in Tab2. Will that affect the code?
Thanks
here's my code:
Private Sub Command550_Click()
Dim sTo, sTel, sClientName
Dim OutApp As Object
Dim OutMail As Object
sTo = Me.Email
sTel = Me.Tel
sClientName = Me.ClientName
Dim sVar(), j
sVar = Array("sTo", "sClientName", "sTell")
For j = LBound(sVar) To UBound(sVar)
If Len(Trim(sVar(j))) = 0 Then
MsgBox "Please enter information for " & sVar(j)
Exit Sub
End If
Next
Set OutApp = CreateObject("Outlook.Appl
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = "Me.Email"
.CC = ""
.BCC = """"
.Subject = "Test"
.HTMLBody = "<HTML>Dear " & Me.ClientName & "<BR>" _
.Send
MsgBox ("Email has been sent successfully")
.Sent
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Inside my form has a tab control, the textboxes are in Tab1 and my button is in Tab2. Will that affect the code?
Thanks
that will work if any of the variables sTo, sTel, sClientName is empty or ""
did you try the codes without anything assign to any of the variables?
also in your code, this is incorrect
sVar = Array("sTo", "sClientName", "sTell")
should be
sVar = Array("sTo", "sClientName", "sTel")
did you try the codes without anything assign to any of the variables?
also in your code, this is incorrect
sVar = Array("sTo", "sClientName", "sTell")
should be
sVar = Array("sTo", "sClientName", "sTel")
ASKER
Hi Capricorn,
I have changed that to "sTel" and also I have left blank for all the variables.
When I click 'Send', the email automatically sent and I did received the email with the blank variables in it.
I have changed that to "sTel" and also I have left blank for all the variables.
When I click 'Send', the email automatically sent and I did received the email with the blank variables in it.
can you upload your db..
but before that, do a compact and repair of your
from VBA window do Debug>Compile
correct any errors raised
try the operation again
but before that, do a compact and repair of your
from VBA window do Debug>Compile
correct any errors raised
try the operation again
ASKER
Hi Capricorn,
I have used a sample from Microsoft and added the code inside. Still didn't work either.
Variables are: ContactFirstName, PhoneNumber and Country (I changed country as email)
Thanks
TS001018467-2-.mdb
I have used a sample from Microsoft and added the code inside. Still didn't work either.
Variables are: ContactFirstName, PhoneNumber and Country (I changed country as email)
Thanks
TS001018467-2-.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's perfect!
Thanks Capricorn!
Thanks Capricorn!
dim sVar(), j
sVar=array("sTo","sCC","sS
for j=lbound(sVar) to ubound(sVar)
if len(trim(sVar(j)))=0 then
msgbox "Please enter information for " & sVar(j)
end if
next