• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sending email from excel

Shabbo

Member
dear sir,
Multiple CC, Dear Sir, I tried to put multiple email addressees, in CC but its taking only 1 address as CC , can you please help to put multiple address as CC.

Code:
Option Explicit
Sub code()

Dim o As Outlook.Application
Set o = New Outlook.Application

Dim omail As Outlook.MailItem
Dim J As Long


For J = 2 To Sheets("sheet1").Range("a100").End(xlUp).Row
Set omail = o.CreateItem(olMailItem)

With omail

.To = Cells(J, 1).Value
.CC = Cells(J, 2).Value
.CC = Cells(J, 3).Value
.CC = Cells(J, 4).Value
.CC = Cells(J, 5).Value
.CC = Cells(J, 6).Value
.CC = Cells(J, 7).Value
.Subject = Cells(J, 11).Value
.Attachments.Add Cells(J, 8).Value
.Body = Cells(J, 9).Value & "," & vbNewLine & vbNewLine & Cells(J, 10).Value
.Send
'send ' if you want to directly send...

End With

Next J

End Sub
 
Last edited by a moderator:

Chihiro

Excel Ninja
Please update your post to nest your code in Code tag using dropdown menu.
62535

You'll need to concatenate multiple emails for CC.

Ex:
Code:
Dim cel As Range, CCStr As String
For Each cel In Range("J2:J7").Cells
    CCStr = IIf(Len(CCStr) = 0, cel.Value, CCStr & ";" & cel.Value)
Next
 

Shabbo

Member
Dear Sir,
Above code work for sending Excel file as attachment from outlook.
Along with attachment I wanted to paste table of that attachment in outlook.
please advice.
 

Chihiro

Excel Ninja
? If you don't know what data to get from workbook. How do you suppose you can grab data?

I'm not a mind reader, I'll need you to detail step by step, your process. And provide sample of what you are trying to do.
 

Shabbo

Member
Dear Sir ,
I have code for sending email as attachment but same file in same email I wanted to send as a table as well.
I mean data should be copied from that file as a range and paste it into outlook,
Code:
Sub code()

Dim o As Outlook.Application
Set o = New Outlook.Application

Dim omail As Outlook.MailItem
Dim J As Long



For J = 2 To Sheets("sheet1").Range("a100").End(xlUp).Row
Set omail = o.CreateItem(olMailItem)

    With omail

        .To = Cells(J, 1).Value
        .CC = Cells(J, 2) & ";" & Cells(J, 3)
        .Subject = Cells(J, 11).Value
        .Attachments.Add Cells(J, 8).Value
        .Body = Cells(J, 9).Value & "," & vbNewLine & vbNewLine & Cells(J, 10).Value
       

        .Display
        'send   ' if you want to directly send...

    End With

Next J

End Sub
 

Attachments

Kenneth Hobson

Active Member
Your code will need to:
1. Open the attachment workbook.
2. Send the range to Ron's RangeToHTML() to create the html string.
3. Then use .htmlbody rather than .body as Ron explains in his examples and files.
4. After the html string is created, you can Close the attachment workbook without saving it.
 

Shabbo

Member
Dear Sir,
There are 100 of files and each files has different range this method look as same as copy and paste from excel file.
 

Kenneth Hobson

Active Member
I guess you are asking if html=copy/paste? If so, the answer is no. It usually looks fairly similar so many people use that method. If you have charts or objects overlaying the range, it will not "copy" it. In that case, one uses a copy/paste as image method using Outlook's WordEditor method rather than .body or .htmBody.

Before you go too far, do one to see if it meets your needs.

As for a 100 files, I guess that means 100 emails? If so, it will likely work but if something goes wrong, you may not know what was sent unless you look in Outlook's Sent Items folder. I like to use a status column and put sent after each successful .Send.
 

Shabbo

Member
Dear Sir, I dont want to put image I wanted to put table of excel in outlook body.

And you are right there will be 100 emails that I can check from outlook sentbox.
 

Kenneth Hobson

Active Member
I don't see a "table" in either of your two files.

Have you tried the RangeToHTML() method that I explained? Ron also has that routine at:
and

You really should try doing one using the method above. After your .Display, on next line put Exit Sub. Though not your attachment file, you can easily test it for just the activesheet's usedrange like:
Code:
.htmlbody = RangeToHTML(activesheet.usedrange)
If wrapping this up as I explained in #8 is too difficult, I can show you that code. It is fairly trivial. The macro recorder can show how to do the #8 small parts that I have not already explained. You can learn more by doing than us doing it all.
 
Top