ItemData

[Martin]

Hello,

I have a list box in a form called "ADName". This selects just two columns,
the first being the bound column. The second is an e-mail address that i
want to use to e-mail an attachment from Excel. I have used ItemData to get
the value of the list box but I need to use the second column with the e-mail
address.

ubADName = Me.ADName.ItemData(vItem)


Can anyone think how to do this? Is ItemData the correct way to get the
value of the e-mail address?

Any help is much appreciated.

Martin

 

[Martin]

Thanks for the response Chris.

I have tried this and it seems that it doesn't pick the correct values.
This is the code so far:

ubADName = Me.ADName.ItemData(vItem)
ubADEmailaddress = Me.ADName.Column(1, Me.ADName.ItemData(vItem))

I have removed all the e-mail addresses just to test and left only two. It
doesn't pick the correct e-mail for the row selected from theubADName list.

Any thoughts?

 

[Douglas J. Steele]

Assuming vItem is how you're addressing the ItemsSelected collection, I
would think that should be

ubADEmailaddress = Me.ADName.Column(1, vItem)

 

[Martin]

Chris,

I can't get it to work but i think it is becuase it is part of a "For Each"
statement. I have copied the code below:

Private Sub ProducePack_Click()
Dim vItem, Column
Dim SourceFile, DestinationFile
Dim XL As Object
Dim outApp As Outlook.Application, outMsg As MailItem

DoCmd.SetWarnings False

Call CurrentDBDir

SourceFile = CurrentDBDir & ".xls"
DestinationFile = ".xls"
FileCopy SourceFile, DestinationFile

ubADName = Null
ubADEmailaddress = Null
intProgress = 0

For Each vItem In Me.ADName.ItemsSelected
ubADName = Me.ADName.ItemData(vItem)
ubADEmailaddress = Me.ADName.Column(1, Me.ADName.ListIndex)

SourceFile = ""
DestinationFile = ""
FileCopy SourceFile, DestinationFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Report
01", DestinationFile, True



Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open DestinationFile
XL.Run "SaveWithPassword" ' Excel macro name
XL.Workbooks.Close
Set XL = Nothing

Set outApp = CreateObject("Outlook.Application")
Set outMsg = outApp.CreateItem(olMailItem)
With outMsg


.To = ubADEmailaddress
'.CC = ""
'.BCC = ""
.Subject = ""
.Body = "" & vbCrLf & vbCrLf & ""
.Attachments.Add "", , 1
.Send
End With

Set outApp = Nothing
Set outMsg = Nothing

Me.intProgress = Me.intProgress + 1
Me.Repaint

Next

Call DoList("C", "ADname")

DoCmd.SetWarnings True

Kill "c:\Temp\[FileName]"

Call Shell("C:\WINDOWS\EXPLORER.EXE ""c:\Temp""", 4)

End Sub

I would try to offer my own solution but I am really stuck with this. Woudl
you be so kind as to offer any further suggestions?

Martin

 

[Martin]

Dougals,

That has worked perfectly. Thank you to you and to Chris.

Martin

Assuming vItem is how you're addressing the ItemsSelected collection, I
would think that should be

ubADEmailaddress = Me.ADName.Column(1, vItem)