pasting values from range into e-mail

[03021979]

I have a code to send en email:

Application.DisplayAlerts = False
Set ol = CreateObject("Outlook.Application")
Set MailItem = ol.CreateItem(olMailItem)
My_File1 = "C:\aaa.txt"
Dim Msg As Variant

ThisRecipient = "Tomasz.Skura@thomson.net"
Msg = "Witam," & Chr(10) & Chr(13) & "Nast?puj?ce zamówienia wprowadzone do systemu:"

With MailItem
.To = "Ewa.Lyczko@thomson.net"
.CC = "*PIAS DMS LOGISTIC"
.Subject = "zamówienia BV wprowadzone do systemu"
.Body = Msg
.Display
'.Attachments.Add My_File1, olByValue, 1, "Montly List1"

End With
Set ol = Nothing
Set MailItem = Nothing
Application.DisplayAlerts = True


I would like to paste values from certain range i.e from a1 to f10 from certain sheet into this e-mail as Msg. Is there a simple way to do this?

 

[Loomah]

Firstly is this a VB app or is it running from within Word/Excel/Powerpoint?

I don't think
Application.DisplayAlerts = False
exists in VB (obviously could be wrong!!!)

If it's not Excel the you will firstly have to create an Excel object.

Beyond that I'm not sure how precisely to get xl ranges into outlook. But things to think about may be the range is an xl object and the data within that range could be treated as an array.

I'm gong to have a little think on this as it's not something I've done before but I'm assuming xl is hosting the code!


;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

Hi again
Here's one REALLY dirty method you could use
after initialising the msg variable add the following code

For Each c In ThisWorkbook.Worksheets(1).Range("A1:B4")
Msg = Msg & vbCrLf & c
Next

and change the range to suit you.

I honestly don't believe this to be the best way as apart from anything else, all it will do is list the data in your range. But it will work!

Again I stress I have done this on the assumption that the code is hosted by Excel. Otherwise it's a different story.

Also, have a look at the FAQs for this forum (I'm sre I saw some relating to Excel data) and definately have a look in the VBA forum707.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[03021979]

Thanks! I already knew this solution but I want the data to be pasted exactly as they are in the sheet, i.e. borders, fonts so I mean to paste 'screenshot' from the certain cell range.

 

[Andrzejek]

Open Excel file where you want your info from and:

    Range("A1:F10").Select
    Selection.Copy

that puts the range into Clipboard, and in VB do

MailItem.SetFocus
SendKeys "^V"

just paste it to your e-mail

HTH

Powodzenia.

---- Andy

 

[03021979]

I don't know where to put it exactly

Set ol = CreateObject("Outlook.Application")
Set MailItem = ol.CreateItem(olMailItem)

With MailItem
.To = "Angelika.Bogumil@thomson.net"
.CC = "Tomasz.Skura@thomson.net"
'.To = "Danuta.Pawlowska@thomson.net"
'.CC = "Bogumil.Angelika@thomson.net;Bozena.Czuba@thomson.net;Marek.Czerniewski@thomson.net;Marek.Ceglowski@thomson.net"
.Subject = "Raport BV, wysy?ka z dnia: " & Date & " do: " & miejsce
.Body = Msg
.Display
.Attachments.Add ActiveWorkbook.FullName
.Send
End With

When I enter the send key code into VB, there appears rutime error 438..

 

[03021979]

Runtime error appears at: MailItem.SetFocus

 

[Andrzejek]

Do you have any object on your VB form, like a text box or Rich Text Box control where you acctually paste the body of the message?

If you would do it 'by hand' - where would you do the Ctrl-V to paste the range from Excel? SetFocus to that control on your Form.

Powodzenia w pracy.

---- Andy

 

[03021979]

The object I would like to paste data is MailItem created by:

Set ol = CreateObject("Outlook.Application")
Set MailItem = ol.CreateItem(olMailItem)

Unfortunalety, this method isn't working for e-mails.