Solved

MsgBox before sending email

Posted on 2010-08-16
14
308 Views
Last Modified: 2013-11-28
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.Application")
    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!
0
Comment
Question by:rutlandict
  • 7
  • 6
14 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33445157
Before the .send command, check all the variables


dim sVar(), j
sVar=array("sTo","sCC","sSubj")

for j=lbound(sVar) to ubound(sVar)
     if len(trim(sVar(j)))=0 then
       msgbox "Please enter information for " & sVar(j)
     end  if
next

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33445194
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


0
 

Author Comment

by:rutlandict
ID: 33445271
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.Application")
    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
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33445287
<Have also tested Capricorn's code before .Send>

post the codes that you used , the whole sub..
0
 

Author Comment

by:rutlandict
ID: 33445345
Hello Capricorn, here's the code:

Private Sub Command550_Click()

    Dim OutApp As Object
    Dim OutMail As Object
   
    Set OutApp = CreateObject("Outlook.Application")
    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!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33445391
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.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>" _
       
        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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33445399
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

Open in new window

0
 

Author Comment

by:rutlandict
ID: 33445612
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.Application")
    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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33445701
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")
0
 

Author Comment

by:rutlandict
ID: 33446274
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33446326
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
0
 

Author Comment

by:rutlandict
ID: 33446639
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
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 33446909
test this


TS001018467-2-.mdb
0
 

Author Comment

by:rutlandict
ID: 33447008
That's perfect!

Thanks Capricorn!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Modal form 11 29
Access query expression 6 20
Why get error when delete all records on a sub-form 2 15
DCount Type Mismatch 2 21
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now