Prev: ActiveX can't create Scripting.FileSystemObject
Next: Reading Registry Keys and Values?
From: MikeGee on 15 Nov 2006 05:21
I am trying to copy a transmittal sheet that I have created in Excel and
paste it into a new email message. Basically I am attempting to replicate the
automatic function that can be activated by File/Send To/Mail Recipient with
VBA Code.

I can copy the cells and then launch an new email, but I cannot work out how
to paste the copied cells into the new email from a Macro running in Excel.

Any assistance is greatly appreciated.

Mike
From: dNagel on 15 Nov 2006 11:57


I pulled this from a site a few days ago but can't recall where...

D.

Load data from an Excel Worksheet
There may be times when you want to generate an email using data from
an application such as Excel. This is one simple illustration of how
that could be done.

In our example we will be using a Workbook with three columns starting
at column A row 1. Each row represents one product in our inventory and
the three columns contains the following data about each item: Part
Number, Name of Part, Number of Items in Inventory. Graphically our
Workbook looks like this:

Part Name Stock
4583586 Fliggalhopper 452
5898547 Looplonger 293

This particular script works by walking down each cell of column 1 till
it finds an empty cell which it assumes is the end of the list of
entries. If your file may contain empty cells then you can use the
Worksheet's UsedRange.Rows.Count property to find the last row in which
an entry is made. Your code would then use a for loop something like this:


rowLast = objSheet.UsedRange.Rows.Count
for x = rowStart to rowLast
' do stuff
next



Function GetData()
Dim x, strTemp, objExcel, objWB

Set objExcel = Wscript.CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open("c:\Acme Inc\Workbooks\Test.xls")
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

' Make Excel visible while debugging
objExcel.Visible = True

' This is the row of our first cell.
x = 1

do while objExcel.Cells(x, 1).Value <> ""
strTemp = strTemp & objExcel.Cells(x, 1).Value & _
Space(10 - Len(objExcel.Cells(x, 1).Value))
strTemp = strTemp & objExcel.Cells(x, 2).Value & _
Space(50 - Len(objExcel.Cells(x, 2).Value))
strTemp = strTemp & objExcel.Cells(x, 3).Value & vbCRLF
x = x + 1
loop

' This will prevent Excel from prompting us to save the workbook.
objExcel.ActiveWorkbook.Saved = True

' Close the workbook and exit the application.
objWB.Close
objExcel.Quit

set objWB = Nothing
set objExcel = Nothing

GetData = strTemp
End Function

' This is our main function.
Dim strBody

Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Inventory report for " & Date
objMessage.From = "me(a)my.com"
objMessage.To = "bossman(a)my.com"
strBody = "Part" & Space(6) & "Item" & Space(46) & "Stock" & vbCRLF

' Here we call the function GetData to populate the body text.
strBody = strBody & GetData

objMessage.TextBody = strBody
objMessage.Send


The code above will produce an email that looks something like this:

To: bossman(a)my.com
From: me(a)my.com
Subject: Inventory report for 3/19/2005

Part Item Stock
4583586 Fliggalhopper 452
5898547 Looplonger 293

MikeGee wrote:
> I am trying to copy a transmittal sheet that I have created in Excel and
> paste it into a new email message. Basically I am attempting to replicate the
> automatic function that can be activated by File/Send To/Mail Recipient with
> VBA Code.
>
> I can copy the cells and then launch an new email, but I cannot work out how
> to paste the copied cells into the new email from a Macro running in Excel.
>
> Any assistance is greatly appreciated.
>
> Mike
>
From: MikeGee on 16 Nov 2006 04:56
Thanks for the code.

The only slight problem is that rather than simply trying to paste data from
cells into Excel I am also attempting to paste formats, hyperlinks etc.

As I said the function of File/Send To/Recipient does exactly what I want
and I am trying to write code that replicates this. I guess the other way of
doing it would be to paste the contents of the clipboard into the body of the
email. But all I can get in Outlook doing this is "True" or "False" rather
than the actual worksheet

"dNagel" wrote:

>
>
> I pulled this from a site a few days ago but can't recall where...
>
> D.
>
> Load data from an Excel Worksheet
> There may be times when you want to generate an email using data from
> an application such as Excel. This is one simple illustration of how
> that could be done.
>
> In our example we will be using a Workbook with three columns starting
> at column A row 1. Each row represents one product in our inventory and
> the three columns contains the following data about each item: Part
> Number, Name of Part, Number of Items in Inventory. Graphically our
> Workbook looks like this:
>
> Part Name Stock
> 4583586 Fliggalhopper 452
> 5898547 Looplonger 293
>
> This particular script works by walking down each cell of column 1 till
> it finds an empty cell which it assumes is the end of the list of
> entries. If your file may contain empty cells then you can use the
> Worksheet's UsedRange.Rows.Count property to find the last row in which
> an entry is made. Your code would then use a for loop something like this:
>
>
> rowLast = objSheet.UsedRange.Rows.Count
> for x = rowStart to rowLast
> ' do stuff
> next
>
>
>
> Function GetData()
> Dim x, strTemp, objExcel, objWB
>
> Set objExcel = Wscript.CreateObject("Excel.Application")
> Set objWB = objExcel.Workbooks.Open("c:\Acme Inc\Workbooks\Test.xls")
> Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
>
> ' Make Excel visible while debugging
> objExcel.Visible = True
>
> ' This is the row of our first cell.
> x = 1
>
> do while objExcel.Cells(x, 1).Value <> ""
> strTemp = strTemp & objExcel.Cells(x, 1).Value & _
> Space(10 - Len(objExcel.Cells(x, 1).Value))
> strTemp = strTemp & objExcel.Cells(x, 2).Value & _
> Space(50 - Len(objExcel.Cells(x, 2).Value))
> strTemp = strTemp & objExcel.Cells(x, 3).Value & vbCRLF
> x = x + 1
> loop
>
> ' This will prevent Excel from prompting us to save the workbook.
> objExcel.ActiveWorkbook.Saved = True
>
> ' Close the workbook and exit the application.
> objWB.Close
> objExcel.Quit
>
> set objWB = Nothing
> set objExcel = Nothing
>
> GetData = strTemp
> End Function
>
> ' This is our main function.
> Dim strBody
>
> Set objMessage = CreateObject("CDO.Message")
> objMessage.Subject = "Inventory report for " & Date
> objMessage.From = "me(a)my.com"
> objMessage.To = "bossman(a)my.com"
> strBody = "Part" & Space(6) & "Item" & Space(46) & "Stock" & vbCRLF
>
> ' Here we call the function GetData to populate the body text.
> strBody = strBody & GetData
>
> objMessage.TextBody = strBody
> objMessage.Send
>
>
> The code above will produce an email that looks something like this:
>
> To: bossman(a)my.com
> From: me(a)my.com
> Subject: Inventory report for 3/19/2005
>
> Part Item Stock
> 4583586 Fliggalhopper 452
> 5898547 Looplonger 293
>
> MikeGee wrote:
> > I am trying to copy a transmittal sheet that I have created in Excel and
> > paste it into a new email message. Basically I am attempting to replicate the
> > automatic function that can be activated by File/Send To/Mail Recipient with
> > VBA Code.
> >
> > I can copy the cells and then launch an new email, but I cannot work out how
> > to paste the copied cells into the new email from a Macro running in Excel.
> >
> > Any assistance is greatly appreciated.
> >
> > Mike
> >
>
 | 
Pages: 1
Prev: ActiveX can't create Scripting.FileSystemObject
Next: Reading Registry Keys and Values?