How to add CC in VB codes while Creating Excel macros to be sent via outlook application?

[Excelviewer]

Hi Friends,

Please can anyone help me out in this below task.

I am creating a macro file on which the excel file contents needs to be sent through outlook application, I have codings to add 'To' field, can anyone help me out to add 'CC' & 'From' fields.

Private Sub CommandButton1_Click()
Dim strbody As String
Set myolapp = CreateObject("Outlook.Application")
Set mailitem = myolapp.createitem(olmailitem)
Set myrecipient = mailitem.Recipients.Add(xyz@gmail.com)
mailitem.Subject = "Answers"
strbody = Sheet4.Range("b3").Value & vbNewLine & vbNewLine & Sheet4.Range("b7").Value & vbNewLine & Sheet4.Range("b8").Value & vbNewLine & Sheet4.Range("b9").Value
mailitem.body = strbody
ActiveSheet.Copy
Set WB = ActiveWorkbook
Filename = "copysheet.xls"
On Error Resume Next
Kill "C:\" & Filename
On Error GoTo 0
WB.SaveAs Filename:="Q:\Shared\MOP Bridge Gas\BG Passback sheet\New Template" & Filename
mailitem.Attachments.Add WB.FullName
mailitem.display
End Sub



Thanks,
Excelviewer

 

[Kyle123]

Which version of outlook are you using?

 

[Excelviewer]

Which version of outlook are you using?

Hi Kyle,

Thanks for your reply, I am currently using 2003 & 2007 version of outlook & Excel.

Regards,
Excelviewer

 

[Norie]

You need to use the Recipients collection of the mail item.

There's an example here Recipients Object [Outlook 2007 Developer Reference]

 

[Kyle123]

Alternatively, does this help?

Private Sub CommandButton1_Click()

Dim wb As Workbook


With CreateObject("Outlook.Application").createitem(0)
    .To = ""
    .cc = "Kyle@gmail.com"
    .bcc = ""
    .SentOnBehalfOfName = "ExcelViewer@gmail.com"
    .Subject = "Answers"
    .body = Sheet4.Range("b3").Value & vbNewLine & vbNewLine & _
            Sheet4.Range("b7").Value & vbNewLine & _
            Sheet4.Range("b8").Value & vbNewLine & _
            Sheet4.Range("b9").Value
    ActiveSheet.Copy
    Set wb = ActiveWorkbook
    wb.SaveAs "Q:\Shared\MOP Bridge Gas\BG Passback sheet\New Template\copysheet.xls"
    .attachements.Add wb.FullName
    .display
End With


End Sub

 

[Excelviewer]

Hi Kyle,

Thanks for your codings.
Its working good but I need small updations on this.

Can you please help me out???
1. I want to add multiple mail ids in TO and CC fields EX: "xyz@gmail.com, kyle@gmail.com , john@gmail.com"
2. Need to move the entire workbook through mail including sheet1,2 & 3
3. No need to save the copy of workbook which has been sent in mail.

Thanks for your Help

Regards,
ExcelViewer

 

[Kyle123]

My code above was just a rewrite of your existing code.

Has the workbook already been saved? If it has, you can use the below:

Private Sub CommandButton1_Click()


Dim wb As Workbook


With CreateObject("Outlook.Application").createitem(0)
    'Either use the recipients collection - default is to Field
    .Recipients.Add "Kyle@gmail.com"
    .Recipients.Add "xyz@gmail.com"
    'For anything other than the to field you'll need to include a type
    With .Recipients.Add("john@gmail.com")
        .Type = 2 '- Set the type for cc
    End With
    '---- or ----
    'You can build the To/CC fields by string
    
    '.To = "Kyle@gmail.com; xyz@gmail.com"
    '.cc = "john@gmail.com"
    .bcc = ""
    .SentOnBehalfOfName = "ExcelViewer@gmail.com"
    .Subject = "Answers"
    .body = Sheet4.Range("b3").Value & vbNewLine & vbNewLine & _
            Sheet4.Range("b7").Value & vbNewLine & _
            Sheet4.Range("b8").Value & vbNewLine & _
            Sheet4.Range("b9").Value
    .attachments.Add ThisWorkbook.FullName
    .display
End With


End Sub

 

[Excelviewer]

THanks dude

Its working well now....

Cheers,
Ashok
ExcelViewer...