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?

 

[dk]

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.

Tip of the iceberg mate! If you want to display the message then change the .send to .display.

Dan

 

[TommyGun]

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

MIND???

You are the email HTML master, and you think I'd mind you tweaking my lil' code!

 

[owendodd]

Hi,

I am just viewing your topic and i think it would be very useful. I have tried running but i get the message Cannot Start Outlook dialog. I do have Outlook 2003, i am jus wondering why it would do this

 

[SUBAWRXSTi]

Include Range and text in email

I am using the following code as in this thread to send emails. I now require it to send a range (B8:G10) and also some text that can be included in the email (eg. This is a test). Thanks for your help. Code below:

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]

are all 6 columns ( B to G) list of receipients? or column B is the list of receipient, C is the subject, D is the body, etc?

 

[SUBAWRXSTi]

The columns of data in B8:G10 is a table of excel data that I want included in the email. I was also wanting other text to be included in the email and perhaps a few more cell references to data...such as merged cell A5:A8.

The list of recipients is in range A1:A10.

 

[Gary Drumm]

What is the trigger?

Great thread.
I'm not very good at reading macros, so can someone please explain?
What is the trigger here?

I am looking for a way to email some inventory data from a row, if a particular item in that row is believed to be obsolete.

Ideally, this would be triggered by an auditor, who enters the word "Yes" in the last cell of that row. That column would be titled "Obsolete".

It would be nice if the receiver of the email would see "Obsolete Plates" as the subject of the e-mail.

Once the email is sent, I would like to automatically move that entire row (now considered obsolete) from worksheet 1, to worksheet 2.

Is this the right thread to get me started?

Thanks,
Gary

 

[aurbo]

Is it possible by using this code to input specific excel cell data into the body of the text? Say... Cells C3:C5

This is a very cool code snip and I'd like to try to incorporate it into my Office lotto pool spreadsheet I am hacking away on..

It would be nice to be able do the two following things:
1. mass email send of todays lottery draw results to the pool members indicating a win or loss.

2. a Private email to a specific member that his/her lottery contribution is running low or has run out.

I am willing to give it a try myself if I can get a nudge in the right direction regarding adding cell data from the spreadsheet

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

Cheers
Aurbo

 

[aurbo]

Ok this is going better than planned, just hung up on a few points.

I have been able to handle the mass email notification of office participants showing recent winings.

But I am stuck on the individual email form; rather than create 10 to 15 separate Sub email() routines, there must be a way I can base the email on the current focus cell and then use it to mode the other cell data

With objTask
        .Subject = "Office Lottery Pool Results"
         StrBody = Sheets("Lotto").Range("A3").Text & ", your current contribution is paid through " & _
              Sheets("Lotto").Range("E3").Text & " leaving you a total of " & _
              Sheets("Lotto").Range("I3").Text & " draws left. " & " Total yearly pool winnings to date = " & _
              Sheets("Lotto").Range("M15").Text
              .Body = StrBody
                
        'Build up a recipient list
        For Each rngCell In Range("C3")
            If Len(rngCell.Value) > 0 Then strRecipients = strRecipients & rngCell.Value & ";"
        Next rngCell
        .to = strRecipients
        .Send
    End With

Range A3 is the name of the person I wish to send the email to, C3, E3, I3 contains the cell data being sent.

I am trying to find a way to get the focus of the active cell from A1 through A15 and based on that change the C,E,I to the corresponding row number.

Any suggestions?


FYI
The modded code for mass email with results.

Sub emailMass()
    Dim objOut As Object
    Dim objTask As Object
    Dim blnCrt As Boolean
    Dim strRecipients As String
    Dim rngCell As Range
    Dim StrBody As String

    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 = "Office Lottery Pool Results"
         StrBody = "Greetings,  This is an auto-generated update on the Office Lotto Pool results for " & Sheets("Lotto").Range("S2").Text & _
         " The Office pool now stands at " & Sheets("Lotto").Range("M15").Text
         .Body = StrBody
                
        'Build up a recipient list
        For Each rngCell In Range("C3:C15")
            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

 

[alcorjr]

Hi, I get a window pop up asking me if it is safe to send the mail.
When i click on yes I get :
"Unable to perform the operation. couls not open the item.Try again"

I try again and get the same.

Now, I got Mozilla thunderbird as my default mail program...

Any ideas?