Using MS Access/VBA email using Access

Advertisement


Question
QUESTION: I am fairly new to VBA and struggle sometimes. However, my problem is with the CC component in the VBA code, shown below

With objEmail
.To = [Email Address]
.Subject = "Maintenance Work Order"
.CC = [CC]
.ReadReceiptRequested = True

The code uses a field to find the email Address and this is fine however, it stops when it doesn't see an email address in the CC field. Not all contacts have a CC to insert in the message.

As I said the whole process works fine when the recipient of the email has both an email address and email addresses for CC's. It does not want to work with just an email address and no cc.

It may seem simple but it is bugging me. Can you please help me?

ANSWER: Hi Charles,

Thanks for the question.  I would add an Is Null If statement for the CC field.

Try the following

If IsNull [CC] or [CC] = "" Then
With objEmail
.To = [Email Address]
.Subject = "Maintenance Work Order"
.ReadReceiptRequested = True
else
With objEmail
.To = [Email Address]
.Subject = "Maintenance Work Order"
.CC = [CC]
.ReadReceiptRequested = True
End If

This would run one or the other depending on whether the CC field was null or not i.e. leave the [CC] line of code out if the [CC] field was Null and include it if it is not.

I have included the 'or [CC] = "" ', because if a user puts a value in the [CC] field and then deletes it, it is no longer Null but rather an empty string.  You need to take both options into consideration when you are writing your VBA code.

Hope this is of some help

Regards



---------- FOLLOW-UP ----------

QUESTION: Julie

Thank you for your solution.

I am still having problems.

I had to change the code for the If IsNull [CC] or [CC] = "" Then

         to

If IsNull(Me![CC]) Or (Me![CC]) = "" Then


and this worked.

However "Else" is saying it has a "Compile Error - Else Without If".

Below is the code for this part

Rem Generate email

If IsNull(Me![CC]) Or (Me![CC]) = "" Then
With objEmail
.To = [Email Address]
.Subject = "Maintenance Work Order"
.ReadReceiptRequested = True
Else
With objEmail
.To = [Email Address]
.Subject = "Maintenance Work Order"
.CC = [CC]
.ReadReceiptRequested = True
End If


Dim mymsg As String

   mymsg = Me.ID & vbCrLf & vbCrLf
   mymsg = mymsg & "Attached is a Maintenance Work Order for your attention and action." & vbCrLf & vbCrLf
    
.Body = mymsg
.Display
.Attachments.Add strAttach1
.Send
End With

Can you help, if not I will understand.

Charles

Answer
Hi Charles,

Thanks for the reply back, you are almost there.

My apologies, I should have told you to add the Me! in.

The problem you have now, is that you need to have a End With in each part of the IF function, and add another With see below ('***):

If IsNull(Me![CC]) Or (Me![CC]) = "" Then
With objEmail
.To = [Email Address]
.Subject = "Maintenance Work Order"
.ReadReceiptRequested = True
End With  '***
Else
With objEmail
.To = [Email Address]
.Subject = "Maintenance Work Order"
.CC = [CC]
.ReadReceiptRequested = True
End With '***
End If


Dim mymsg As String

  mymsg = Me.ID & vbCrLf & vbCrLf
  mymsg = mymsg & "Attached is a Maintenance Work Order for your attention and action." & vbCrLf & vbCrLf

With objEmail '***
   
.Body = mymsg
.Display
.Attachments.Add strAttach1
.Send
End With

Try that and see if that works.

Regards

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Julie Misson

Expertise

My area of expertise is Microsoft Access 2000-2007. I have been building microsoft databases for the past twelve years. I would be competent in answering questions from novice Access 2000 users to the more advanced including VBA. If I cannot help, I am more than likely be able to point you to websites that can.

Experience

I have a Microsoft Access business where I build database for small businesses. These are usually one off designs, where there is no off the shelf software avaliable to meet the business needs. I also teach Microsft Access to beginners. I am the owner of the www.simply-access.com website.

Education/Credentials
Self taught in Microsoft Access. Have done some units in normalisation and SQL, but most of what I know I have learnt from books originally and more recently the Internet.

©2016 About.com. All rights reserved.