emailing from excel

[ammdumas]

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?

 

[TommyGun]

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]

Wow. Answer from the source. ray: 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]

Sorry...this code is sending a task not an email.

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]

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.

 

[TommyGun]

.Body = Range("C3").Text

 

[ammdumas]

Thanks. You are a genius ray:

 

[Erick]

Is it possible to have a cell range for recipient. For example if I have a list of recipients in cells "A1:A10".

 

[dk]

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:-

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]

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.