Attachmate, Excel & Outlook 2

[Christadelphian]

Hello all,

I am currently experiencing a problem and am wondering if anyone can help please?
To cut a long story short, I am writing a macro which will scrape several details from Attachmate Extra! into an Excel spreadsheet. (Cells A1 - A5). This I have done fine.
An example being......

Database = Sess0.Screen.GetString(24, 79, 2)
ObjWorksheet.Cells(2,2).Value = Database
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

The macro then writes an email and populates the email with the information from the afore-mentioned cells A1 - A5. However I do not know the syntax within Extra Basic to have the Cell Value pasted into Outlook.
The Macro will write the email how I want it, just minus the populated data.

Anyone able to point me in the right direction for how to do this please? A simple copy and paste won't suffice as the Macro reads all information from Extra! before generating the email.

I don't really wish to post my entire code, as it contains sensitive data, however I will do if anyone thinks it will help? It would be greatly appreciated.

I hope I have explained myself clearly enough.

Thanks

 

[Christadelphian]

I have enclosed a copy of my code (editied slightly for sensitive info). The bits inbetween the stars(*) and slashes(/) are the parts of the email I wish to paste a variable.


'--------------------------------------------------------------------------------
' This macro was created by Christadelphian
' Session Document: "CSS.EDP"
' Date: Saturday, January 19, 2008 09:35:14
' User:
'--------------------------------------------------------------------------------

' Global variable declarations
Global g_HostSettleTime%
Global g_szPassword$

Sub Main()

'--------------------------------------------------------------------------------
Dim Explorer As Object
Dim Sess0 As Object
Dim Sys As Object
Dim Sessions As Object
Dim System As Object
Dim ObjExcel As Object
Dim ObjWorkbook As Object
Dim ObjWorksheet As Object

Dim CorrectPage As String
Dim result As String
Dim Database As String
Dim CSSOrder As String
Dim XXXX As String
Dim XXX As String
Dim XXXX As String
Dim XXXX As String
Dim XXXXXXXX As String
Dim ServingCode As String

' Define Mail Variables
Dim oOutlook As Object
Dim oMailItem As Object
Dim BodyText As String
Dim BodyText2 As String

Dim CellCount As Integer

SessName0$ = "CSS.Edp"

Set System = CreateObject("EXTRA.System") ' Gets the system object
If (System is Nothing) Then
Msgbox "Could not create the EXTRA System object. Stopping macro playback."
STOP
End If
Set Sessions = System.Sessions

If (Sessions is Nothing) Then
Msgbox "Could not create the Sessions collection object. Stopping macro playback."
STOP
End If
'--------------------------------------------------------------------------------
' Set the default wait timeout value
g_HostSettleTime = 300 ' milliseconds

OldSystemTimeout& = System.TimeoutValue
If (g_HostSettleTime > OldSystemTimeout) Then
System.TimeoutValue = g_HostSettleTime
End If

' Get the necessary Session Object
Set Sess0 = System.ActiveSession
If (Sess0 is Nothing) Then
Msgbox "Could not create the Session object. Stopping macro playback."
STOP
End If

If Not Sess0.Visible Then Sess0.Visible = TRUE
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)


'---------------------------------------------------------------------------------
CorrectPage = Sess0.Screen.GetString(01, 35, 13)

If CorrectPage ="DISPLAY ORDER" Then


Set ObjExcel = CreateObject("Excel.Application")
Set ObjWorkbook = ObjExcel.Workbooks.Open("H:\Workbook1.xls")
Set ObjWorksheet = ObjWorkbook.Sheets("Sheet1")

ObjExcel.Visible = True

' Get the Order ID
CSSOrder = Sess0.Screen.GetString(03, 58, 6)
ObjWorksheet.Cells(2, 1).Value = CSSOrder
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("<Reset><Home>DIO<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

' Get the database ID
Database = Sess0.Screen.GetString(24, 79, 2)
ObjWorksheet.Cells(2,2).Value = Database
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("<Reset><Home>DO<Tab>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys(CSSOrder)
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys(Database)
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

' Get the XXX
XXX = Sess0.Screen.GetString(10, 44, 8)
ObjWorksheet.Cells(2,3).Value = XXX
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("<Reset><Home>DCS<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

' Get the XXXX Number
XXXX = Sess0.Screen.GetString(03, 61, 13)
ObjWorksheet.Cells(2,4).Value = XXXX
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

' Get the Building Name
BuildingName = Sess0.Screen.GetString(03, 18, 15)
ObjWorksheet.Cells(2,5).Value = BuildingName
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("<Reset><Home>DCS<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Reset><Home>MC<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

' Get Serving Code
ServCode = Sess0.Screen.GetString(22, 22, 5)
ObjWorksheet.Cells(2,6).Value = ServCode
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)

Sess0.Screen.Sendkeys("<Reset><Home>DO<Tab>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys(CSSOrder)
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys(Database)
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)
Sess0.Screen.Sendkeys("<Enter>")
Sess0.Screen.WaitHostQuiet(g_HostSettleTime)


Set oOutlook = CreateObject("Outlook.Application")
Set oMailItem = oOutlook.CreateItem(olMailItem)
With oMailItem
.To = ""
.Subject = "Response Required:"
.HTMLBody = "The CSS Ref Is: */ CSSOrder \*" & _
"The Serving Code Is: */ ServingCode \* & _
"The Building Name Is: */BuildingName *\ &_
"etc......."
.Display
End With


Else
MsgBox "You must be in DO screen to run this command. Please navigate to the appropriate page."
End If

System.TimeoutValue = OldSystemTimeout

ObjExcel.Displayalerts = false
ObjWorkbook.Save
ObjWorkbook.Close
ObjExcel.Quit

Set ObjWorksheet = Nothing
Set ObjWorkbook = Nothing
Set ObjExcel = Nothing
Set oOutlook = Nothing
Set oMailItem = Nothing
Exit Sub

End Sub

 

[SkipVought]

Hi,

.HTMLBody = "The CSS Ref Is: " & CSSOrder & vbLf & _
                "The Serving Code Is: " & ServingCode & vbLf & _
                "The Building Name Is: " & BuildingName & vbLf

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[Christadelphian]

Thanks Skip.... Much appreciated!

I knew it had something to do with antpersand's.

Thanks once again!

:-D