emailing through outlook via macro

[Cease]

Hey guys,
I try to find these things before posting to save you guys the work...

I'm still very much a newbie with code, but I've found this code:

Sub SendEmail()
    Dim OutlookApp As Object
    Dim MItem As Object
    Dim cell As Range
    Dim email_ As String
    Dim subject_ As String
    Dim body_ As String
    Dim attach_ As String
 
     'Create  Outlook object
    Set OutlookApp = CreateObject("Outlook.Application")
 
     ' Loop through the rows
    For Each cell In Columns("a").Cells.SpecialCells(xlCellTypeConstants)
 
        email_ = cell.Value
        subject_ = cell.Offset(0, 1).Value
        body_ = cell.Offset(0, 2).Value
 
 
         'Create Mail Item and send it
        Set MItem = OutlookApp.CreateItem(0)
        With MItem
            .To = email_
            .Subject = subject_
            .Body = body_
            .Send
        End With
    Next
End Sub

Where column A= email address, B= subject and C=body - and it works great.

What do I need to change to have it pull data from a different sheet?

I'm trying to have a macro on sheet 1 email data from sheet 2?

Also what do I need to change if there are multiple cells in column c that I want in the body of the email?

Any help or direction would be greatly appreciated!

 

[Cease]

Ok, I found how to have it mail from another sheet by having the macro on the sheet with the data to send, and a link to it from the initial page.

Can anyone tell me what I need to change in the code to have multiple cells in the body of the email?

Also I'm running excel 2007.

Thanks again in advance for any help!

 

[DippNCope]

I use this, The parts in red should help you. Good luck. I pieced this together from tips I picked up on Mr Excel. This site has helped me a great deal and I hope this helps you

Sub Mail_yank_case_Outlook()
'Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    [COLOR=Red]Dim strbody As String[/COLOR]
    Dim Email As String
    Dim TechName As String
    Dim LDAPName As String
        Dim r As Integer, x As Double
    For r = 2 To 2 'data in rows 2
'       Get the email address tech name and current user name from ldap
' LDAP name is pulled from macro that runs when this spreadsheet is opened and placed in row 2
        Email = Cells(r, 5)
        TechName = Cells(r, 2)
        LDAPName = Cells(r, 7)
        



Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    [COLOR=Red]strbody = "Hi," & vbNewLine & vbNewLine & _
              "I am part of the level 2 deskside team.  We've received your ticket, Case # ______ and I have placed the case in my list while " & TechName & " is out." & vbNewLine & _
              "If you have any questions, please feel free to reply to this email and I will be happy to assist you" & vbNewLine & _
              "Thank you, " & vbNewLine & _
              LDAPName[/COLOR]

    On Error Resume Next
    With OutMail
        .To = "Change to Users SSO for case you are assigning"
        .CC = Email
        .BCC = ""
        .Subject = "Helpdesk Case"
        [COLOR=Red].Body = strbody[/COLOR]
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Display   'or use .Send
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
    Next r
End Sub

 

[Cease]

Thanks for the reply. It's right along what I'm going for (and I'll keep this as a reference cause that is awesome), but I can't seem to figure out the syntax to change to do what it is I'm looking for.

Here's what I'm trying:

Sub SendEmail2()
Dim OutlookApp As Object
Dim MItem As Object
Dim cell As Range
Dim email_ As String
Dim subject_ As String
Dim body_ As String
Dim attach_ As String
 
'Create Outlook object
Set OutlookApp = CreateObject("Outlook.Application")
 
' Loop through the rows
For Each cell In Range("a3:a5").Cells.SpecialCells(xlCellTypeConstants)
 
email_ = cell.Value
subject_ = cell.Offset(0, 1).Value
body1_ = range.Offset(0, 2)&(1, 2).Value
 
 
'Create Mail Item and send it
Set MItem = OutlookApp.CreateItem(0)
With MItem
.To = email_
.Subject = subject_
.Body = body1_
.Display
End With
Next
End Sub

Just trying to get the body to be cells c3:c4
Something like that.

Thanks again,

 

[DippNCope]

I am not sure how to go about that, hopefully one of the many experts on this site can help you.

This site may help you more. this link is for emailing a range
http://www.rondebruin.nl/mail/folder1/mail4.htm

 

[Cease]

Thanks again DippNCope!

With the link and your previous post I was able to piece together this little number:

Sub SendEmail2()
    Dim OutlookApp As Object
    Dim MItem As Object
    Dim cell As Range
    Dim email_ As String
    Dim subject_ As String
    Dim body_ As String
    Dim attach_ As String
 
     'Create  Outlook object
    Set OutlookApp = CreateObject("Outlook.Application")
 
     ' Loop through the rows
    For Each cell In Range("a3:a5").Cells.SpecialCells(xlCellTypeConstants)
 
        email_ = cell.Value
        subject_ = cell.Offset(0, 1).Value
        body1_ = Cells.Range("c3").Value
        body2_ = Cells.Range("c4").Value
 
 
         'Create Mail Item and send it
        Set MItem = OutlookApp.CreateItem(0)
        With MItem
            .To = email_
            .Subject = subject_
            .Body = body1_ & vbNewLine & body2_
            .Display
        End With
    Next
End Sub

posts multiple cells one under the other in an email.

Now I have another question but I'll set that as a new post; thanks again for all your help!