Sending email from excel

[Shabbo]

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.

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

 

[Chihiro]

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


You'll need to concatenate multiple emails for CC.

Ex:

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]

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]

Have a read of Ron de Bruin's article on the subject.

https://www.rondebruin.nl/win/s1/outlook/bmail2.htm

 

[Shabbo]

this will not work, i dont have worksheet name, i have path for files.

 

[Chihiro]

? 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]

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,

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

 

[Kenneth Hobson]

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]

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]

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]

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]

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:

OfficeTalk: Using the Excel Object Model to Send Workbooks and Ranges through E-Mail with Outlook (Part 2 of 2)

docs.microsoft.com

and

Mail Range/Selection in the body of the mail

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:

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