Problems sending e-mails with VB 1

[bmcilroy]

I've created a program in Excel that has VB driving automation. I have two separate modules that work perfectly well. The first programtically prints adobe files, saves them and the Excel file to designated folders, opens Access and runs a macro to import data from the Excel file into Access. The second module e-mails the printed adobe files to designated e-mail addresses. I WANT TO COMBINE THESE MODULES INTO ONE. Unfortunately I can't get them to work together. I apologize for my novice VB abilities, but I'm hoping some nice sole can help me.

 

[bmcilroy]

Oops - forgot to post code. Here are the links to my .rtf files that have the two modules.

First module:

http://truthbenefits.com/Forms/Initial_Quote.rtf

Second Module:

http://truthbenefits.com/Forms/Initial_Quote_2.rtf

 

[TonyJollans]

I haen't looked at your code closely but what goes wrong when you just run the second code immediately after the first?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[bmcilroy]

Tony - My second set of code does not require defining my named cells (example: email = Worksheets("Input").Range("I5").Value). My first set I do have to define (Example: Dim email As String). After inserting the second set of code and defining all Strings, I'm getting a "Compile error: variable not defined" at the start of my e-mail sequence which has the following code:
___________________________________________________________

Set NewMail = CreateObject("Outlook.Application")
Set objmail = NewMail.CreateItem(olMailItem)
With objmail
.to = email
.Subject = "Health Insurance Rates for " & Name
.Body = emailtext
.Attachments.Add PDF1
.Attachments.Add PDF2
.Attachments.Add PDF3
.Attachments.Add "\\Our_Desktop\shareddocs\Truth Benefits\Explanations, Examples & Definitions.pdf"

If Worksheets("Data").Range("O5").Value > 19 Then

.Attachments.Add Pdf8

Else

End If

.Attachments.Add "\\Our_Desktop\shareddocs\Truth Benefits\Truth Benefits Brochure.pdf"
.Send

End With

Set NewMail = Nothing
______________________________________________________

Thanks for ALL your help!

 

[TonyJollans]

The reason you are having this error is because you have Option Expicit specified at the beginning of the first module, but not the second. This is a good thing; it highlights potential errors earlier rather than later and what you have to do is make sure all your variables are defined.

As a bare minimum you can always just do ...

Dim newMail
Dim objmail

In this case as they are both Object references you can do a bit better with ...

Dim newMail As Object
Dim objmail As Object

Or, if you have a reference set to Outlook, you can define them fully as ...

Dim newMail As Outlook.Application
Dim objmail As Outlook.MailItem

If you don't have a reference to Outlook you will find that the constant olMailItem is not defined and will need to hard code its value of 0.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[bmcilroy]

Tony - When I define them as:

Dim newMail As Outlook.Application
Dim objmail As Outlook.MailItem

I get the following message: "Compile error: User-defined type not defined" at the Dim statement

When I define them the other two ways, I'm
getting the same error message at olMailItem. Based upon your last advise - how do I hard code olMailItem to zero and what does that do?

 

[TonyJollans]

Two solutions.

1. Generally the better if you have no reason not to do it (I'm not going into details at the moment). In the VB Editor select Tools > References from the menu. Scroll down the list and check "Microsoft Outlook n.n Library" (where n.n represents your version - you will probably only have one choice). Click OK and the code should work.

2. If you can't (or don't want to) use the reference, code this way instead ...

Dim newMail As Object
Dim objmail As Object
Set NewMail = CreateObject("Outlook.Application")
Set objmail = NewMail.CreateItem(0)

olMailItem is just a constant with a value of zero. Hard coding 0 makes no difference to the way it runs (just to the ease of coding).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[bmcilroy]

Tony - YOU ARE AWESOME!!!

Everything works GREAT!

 

[bmcilroy]

Tony - Please go to my website (address above) and find my e-mail address. I need your mailing address so I can send you a "small" thank you present.

 

[TonyJollans]

Glad you got it working.

There is absolutely no need to reward me in any way for helping you. It is always appreciated when someone posts back here to say thank you and to let us know that they have succeeded in their aims but that is all that is wanted.

Should you be happy to make any financial contribution please donate to the site here, but that, too, is entirely optional.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[firefytr]

I would also have a test to see if Outlook is already open. You can do this either with a function or just all by itself in the code ...

On Error Resume Next
Set NewMail = GetObject(, "Outlook.Application")
If Err <> 0 Then
    Set NewMail = CreateObject("Outlook.Application")
    Err.Clear
    On Error GoTo 0
End If

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[bmcilroy]

Tony,

Since you're clearing a good programmer, can you help me with some e-mail automation within Access? Please see thread below.

thread707-1269679