• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

Sending emails using outlook without defining TO

Hi,

I am using this code to send emails using outlook. I need to modify this code to open outlook with all completed except the TO statement. If i left it in blank, then gives an error! Any idea?

On Error Resume Next
Dim objOL As Object
    
    'check if Outlook is running
    Set objOL = GetObject(, "Outlook.Application")

    'on error start Outlook
    If Err.Number <> 0 Then                                          '
        Err.Clear
        Shell "C:\Program Files\Microsoft Office\Office14\outlook.exe", vbMinimizedFocus
        DoEvents
    End If

Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(0)
With olMsg
    
    .Subject = "Subject"
    .body = "Bodytext"
    .To = ""
    '.CC = Recipient
    .Attachments.Add "c:\test.xlsx"
.Attachments.Add "c:\test2.xlsx"


    

    .Send
    
End With

FIN:
Set olMsg = Nothing
Set olApp = Nothing

Open in new window

0
joyacv2
Asked:
joyacv2
1 Solution
 
mlongohCommented:
What is the error?
0
 
joyacv2Author Commented:
the TO field, cannot be blank
0
 
byundtCommented:
Try it like this:
Sub OutlookMailer()
Dim objOL As Object, olMsg As Object
    
    'check if Outlook is running
    Set objOL = GetObject(, "Outlook.Application")

    'on error start Outlook
    If Err.Number <> 0 Then                                          '
        Set objOL = CreateObject("Outlook.Application")
    End If

Set olMsg = objOL.CreateItem(0)
With olMsg
    
    .Subject = "Subject"
    .body = "Bodytext"
    .To = ""
    '.CC = Recipient
    .Attachments.Add "c:\test.xlsx"
    .Attachments.Add "c:\test2.xlsx"
    .display
End With
Set olMsg = Nothing
Set objOL = Nothing
End Sub

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
byundtCommented:
I changed your code to use olMsg.Display rather than olMsg.Send. The .Display method brings up an Outlook window so you can enter the To address manually. The .Send method tries to send the email--which will obviously fail without an address.

I also corrected some consistency errors regarding objOL (you sometimes used olApp in its place).

I also used late-binding to create a reference to Outlook if it wasn't already running. You hard-coded a path to the application, which means that someone using a different version will break the macro.
0
 
Martin LissOlder than dirtCommented:
Why do you not want the 'To' field to be blank?
0
 
byundtCommented:
I found a runtime error if Outlook was not already running. The version below fixes that error.
Sub OutlookMailer()
Dim objOL As Object, olMsg As Object
    
    'check if Outlook is running
On Error Resume Next
Set objOL = GetObject(, "Outlook.Application")
On Error GoTo 0

    'on error start Outlook
If objOL Is Nothing Then Set objOL = CreateObject("Outlook.Application")

Set olMsg = objOL.CreateItem(0)
With olMsg
    
    .Subject = "Subject"
    .body = "Bodytext"
    .To = ""
    '.CC = Recipient
    .Attachments.Add "c:\test.xlsx"
    .attachments.Add "c:\test2.xlsx"
    .display
End With
Set olMsg = Nothing
Set objOL = Nothing
End Sub

Open in new window

0
 
joyacv2Author Commented:
perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now