Excel VBA - How to get Sender's email address?

[xlStar]

Just a quickie, I have created a form on Excel which when completed by the recipitant is mailed back to me via a button which I have placed on the form and written the macro for.


What I wondered was you might know how I can get the users' e-mail address. So that when they click the send button on the form and then message is sent from excel with the form attached can you make it so that a cell (e.g. Range “A1”) contains the senders email address?

Sub SendEmail()

ESubject = "This is a test email"
SendTo = "name.surname@mycompany.come"
Ebody = "Testing VBA's ability to send an email."
NewFileName = "C:\My Documents\TestFile.xls"

Set App = CreateObject("Outlook.Application")

'Update Range("A1) with Sender's email address goes here



Set itm = App.CreateItem(0)
With itm
.Subject = ESubject
.To = SendTo
.Body = Ebody
.Attachments.Add (NewFileName)
.send
End With


Set App = Nothing
Set itm = Nothing
End Sub

 

[firefytr]

Hi there,

How about something like this ...

Sub SendEmail()
    Dim App As Object, Itm As Object, blnCreated As Boolean
    Dim eSubject As String, eBody As String, NewFileName As String
    eSubject = "This is a test email"
    eBody = "Testing VBA's ability to send an email."
    NewFileName = "C:\My Documents\TestFile.xls"
    Set App = GetObject(, "Outlook.Application")
    blnCreated = False
    If Err <> 0 Then
        Set App = CreateObject("Outlook.Application")
        blnCreated = True
    End If
    Set Itm = App.CreateItem(0)
    Itm.Subject = eSubject
    Itm.To = Sheets("YourSheetName").Range("A1").Value
    Itm.Body = eBody
    Itm.Attachments.Add (NewFileName)
    Itm.Send
    If blnCreated = True Then App.Quit
End Sub

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[xlStar]

Not quite right.

The User is sending the email attachment to me. I wanted them to email the attachment to me but their email address needed to be on cell A1 on the attached file.

i.e Itm.To = myname.mysurname@mycompany.com

is there any Itm.From available.

Range("A1").Formula = Itm.From

 

[TonyJollans]

I'm not sure you can do it with Outlook. It deliberately keeps e-mail addresses away from VBA code (since 2000 SP2). It may be possible with CDO but I don't know how. Aternatively it may be possible to pick up the sender on receipt (although that may not help you if the attachment has been separated from the e-mail by the time you process it).

Do you particularly need the e-mail address to identify the sender? If you are working on a corporate system is there any other id you could use?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association