emailing from excel

ammdumas

Active Member
Joined
Mar 14, 2002
Messages
469
Trying to email from excel. I got this from TommyGun...

Sub email()

Dim objOut As Outlook.Application
Dim objTask As Outlook.TaskItem
Dim blnCrt As Boolean

On Error GoTo CreateOutlook
Set objOut = GetObject(, "Outlook.Application")

CreateItem:
On Error GoTo 0

Set objTask = objOut.CreateItem(olTaskItem)

With objTask
.Assign
.Subject = "Test"
.Body = "This is a test email from excel, let me know if it worked...Austin " '& Format(Now + 10, "mm/dd/yy")
'.DueDate = CDate(Now + 10)
.Recipients.Add ("email@domain.com")
.Send
End With

If blnCrt = True Then objOut.Quit

Set objTask = Nothing
Set objOut = Nothing

Exit Sub

CreateOutlook:
Set objOut = CreateObject("Outlook.Application")
blnCrt = True
Resume CreateItem

End Sub

However, I get an error right off the bat with the line
'Dim objOut As Outlook.Application'
Error message is "User-defined type not defined"

I checked another post that said in Tools....References make sure 'Microsoft Office 9.0 Object Library' is checked. It is but it still doesn't work. What is the solution? :oops:
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

TommyGun

MrExcel MVP
Joined
Dec 9, 2002
Messages
4,202
Updated code that does not require a reference to be set...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> email()
    <SPAN style="color:#00007F">Dim</SPAN> objOut <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> objTask <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> blnCrt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>

    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> objOut = GetObject(, "Outlook.Application")
    <SPAN style="color:#00007F">If</SPAN> objOut <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> objOut = CreateObject("Outlook.Application")
        blnCrt = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">If</SPAN> objOut <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            MsgBox "Unable to start Outlook."
            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0

    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> objTask = objOut.CreateItem(olTaskItem)
    <SPAN style="color:#00007F">If</SPAN> objTask <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">If</SPAN> blnCrt <SPAN style="color:#00007F">Then</SPAN> objOut.Quit
        <SPAN style="color:#00007F">Set</SPAN> objOut = <SPAN style="color:#00007F">Nothing</SPAN>
        MsgBox "Unable to create task."
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0

    <SPAN style="color:#00007F">With</SPAN> objTask
        .Assign
        .Subject = "Test"
        .Body = "This is a test email from excel, let me know if it worked...Austin " <SPAN style="color:#007F00">'& Format(Now + 10, "mm/dd/yy")</SPAN>
        <SPAN style="color:#007F00">'.DueDate = CDate(Now + 10)</SPAN>
        .Recipients.Add ("email@domain.com")
        .Send
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

    <SPAN style="color:#00007F">Set</SPAN> objTask = <SPAN style="color:#00007F">Nothing</SPAN>

    <SPAN style="color:#00007F">If</SPAN> blnCrt = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> objOut.Quit
    <SPAN style="color:#00007F">Set</SPAN> objOut = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

ammdumas

Active Member
Joined
Mar 14, 2002
Messages
469
Wow. Answer from the source. :pray: Cool.

Got further but another error popped up on the line .Assign

Runtime error 438 "Object does not support this property or method"

With objTask
.Assign
.Subject = "Test"
.Body = "This is a test email from excel, let me know if it worked...Austin " '& Format(Now + 10, "mm/dd/yy")
'.DueDate = CDate(Now + 10)
.Recipients.Add ("email@domain.com")
.Send
End With

Is that line required? How does it fit in the scheme of things?
 

TommyGun

MrExcel MVP
Joined
Dec 9, 2002
Messages
4,202
Sorry...this code is sending a task not an email. :oops:

This should work for emails.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> email()
    <SPAN style="color:#00007F">Dim</SPAN> objOut <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> objTask <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> blnCrt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>

    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> objOut = GetObject(, "Outlook.Application")
    <SPAN style="color:#00007F">If</SPAN> objOut <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">Set</SPAN> objOut = CreateObject("Outlook.Application")
        blnCrt = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">If</SPAN> objOut <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
            MsgBox "Unable to start Outlook."
            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0

    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> objTask = objOut.CreateItem(0)
    <SPAN style="color:#00007F">If</SPAN> objTask <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">If</SPAN> blnCrt <SPAN style="color:#00007F">Then</SPAN> objOut.Quit
        <SPAN style="color:#00007F">Set</SPAN> objOut = <SPAN style="color:#00007F">Nothing</SPAN>
        MsgBox "Unable to create task."
        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0

    <SPAN style="color:#00007F">With</SPAN> objTask
        .Subject = "Test"
        .Body = "This is a test email from excel, let me know if it worked...Austin " <SPAN style="color:#007F00">'& Format(Now + 10, "mm/dd/yy")</SPAN>
        <SPAN style="color:#007F00">'.DueDate = CDate(Now + 10)</SPAN>
        .Recipients.Add ("email@domain.com")
        .Send
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>

    <SPAN style="color:#00007F">Set</SPAN> objTask = <SPAN style="color:#00007F">Nothing</SPAN>

    <SPAN style="color:#00007F">If</SPAN> blnCrt = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN> objOut.Quit
    <SPAN style="color:#00007F">Set</SPAN> objOut = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>
 

ammdumas

Active Member
Joined
Mar 14, 2002
Messages
469

ADVERTISEMENT

Yay. It worked.

One more question. How would I throw cell data into the body of the email. Can I just plug in 'C3' or do I have to dim it into a string?

Austin.
 

ammdumas

Active Member
Joined
Mar 14, 2002
Messages
469

ADVERTISEMENT

Thanks. You are a genius :pray:
 

Erick

Active Member
Joined
Feb 26, 2003
Messages
360
Is it possible to have a cell range for recipient. For example if I have a list of recipients in cells "A1:A10".
 

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Erick said:
Is it possible to have a cell range for recipient. For example if I have a list of recipients in cells "A1:A10".

Hopefully Tommy won't mind me replying. This code will send the email to addresses stored in cells A1:A10:-

Code:
Sub email()
    Dim objOut As Object
    Dim objTask As Object
    Dim blnCrt As Boolean
    Dim strRecipients As String
    Dim rngCell As Range

    On Error Resume Next
    Set objOut = GetObject(, "Outlook.Application")
    If objOut Is Nothing Then
        Set objOut = CreateObject("Outlook.Application")
        blnCrt = True
        If objOut Is Nothing Then
            MsgBox "Unable to start Outlook."
            Exit Sub
        End If
    End If
    On Error GoTo 0

    On Error Resume Next
    Set objTask = objOut.CreateItem(0)
    If objTask Is Nothing Then
        If blnCrt Then objOut.Quit
        Set objOut = Nothing
        MsgBox "Unable to create task."
        Exit Sub
    End If
    On Error GoTo 0

    With objTask
        .Subject = "Test"
        .Body = "This is a test email from excel, let me know if it worked...Austin " '& Format(Now + 10, "mm/dd/yy")
        '.DueDate = CDate(Now + 10)
        
        'Build up a recipient list
        For Each rngCell In Range("A1:A10")
            If Len(rngCell.Value) > 0 Then strRecipients = strRecipients & rngCell.Value & ";"
        Next rngCell
        .to = strRecipients
        .Send
    End With

    Set objTask = Nothing

    If blnCrt = True Then objOut.Quit
    Set objOut = Nothing
End Sub
 

Erick

Active Member
Joined
Feb 26, 2003
Messages
360
This is so cool.

Is it possible to create an e-mail but not send it. I just want to to create a bcc recipeint list from an excel range but want to type the actual body and then send it manually.

Thanks.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,565
Messages
5,765,140
Members
425,263
Latest member
alcat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top