Solved

Sending emails using outlook without defining TO

Posted on 2013-05-11
7
275 Views
Last Modified: 2013-05-11
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
Comment
Question by:joyacv2
7 Comments
 
LVL 12

Expert Comment

by:mlongoh
Comment Utility
What is the error?
0
 
LVL 1

Author Comment

by:joyacv2
Comment Utility
the TO field, cannot be blank
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Why do you not want the 'To' field to be blank?
0
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:joyacv2
Comment Utility
perfect!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

771 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

11 Experts available now in Live!

Get 1:1 Help Now