inserting user email address

[stephenscott]

I have a excel template and am looking to insert the individual users email into a cell automatically.

Is there a user profile within windows or excel where I can capture the specific address or

Do I need to setup a sheet with all user names and email addresses?, if so how do I connect that list with the specific user to automatically populate the field?

Any advice would be much appreciated.

 

[bensonsearch]

This is exactly what I have but I am getting Runtime error 438 Object doesn't
support this property or method
Username is Stephen and I have a stephen.jpeg file in C:


Private Sub Workbook_Open()
Dim asd As Variant
Dim dsa As String
Dim filepath As String
filepath = "C:\"
asd = "@hotmail.com"
dsa = (Environ$("Username"))
ThisWorkbook.Sheets("Sheet1").Range("H51").Value = _
dsa & asd
ThisWorkbook.Sheets("Sheet1").Range("G54").Pictures.Insert _
filepath & dsa & ".jpeg" 'if this doesnt work remove brackets
End Sub

my apologies, didnt test the code.

use the below instead

ThisWorkbook.Sheets("Sheet1").Range("G54").Select
ActiveSheet.Pictures.Insert (filepath & dsa & ".jpg")

but personally inserting images inst a good go, they insert over cells not so much into. can text do the trick? or has to be image?

 

[stephenscott]

This seems to do the trick on my test template. Thanks !

I am in need of a "signature file" and beleive image is my only option will test further.

regarding the email component , would I be able to add the mailto: within the macro ie mailto:stephen@hotmail.com in order to make it a hyperlink to open the mail client ? or can I jest set the cel format ?

Thanks for all your help

 

[stephenscott]

Just tried the macro on a excel template and it stoped at the following line below

Private Sub Workbook_Open()
Dim asd As Variant
Dim dsa As String
Dim filepath As String
filepath = "C:\My Documents"
asd = "@hotmail.com"
dsa = (Environ$("Username"))
ThisWorkbook.Sheets("Sheet1").Range("H51").Value = _
dsa & asd
ThisWorkbook.Sheets("Sheet1").Range("L51").Select
ActiveSheet.Pictures.Insert (filepath & dsa & ".jpg")

End Sub

 

[bensonsearch]

The filepath needs to end with a \
Eg:

Filepath = "C:\My Documents\"

Otherwise you are not calling the folder and the error you are getting would be something like file doesn't exist.

Cheers

 

[stephenscott]

Unfortunately I am getting a New error

Runtime error '1004':
Insert method of pictures class failed

It is hanging on the line shown in Red

Private Sub Workbook_Open()
Dim asd As Variant
Dim dsa As String
Dim filepath As String
filepath = "C:\My Documents\"
asd = "@hotmail.com"
dsa = (Environ$("Username"))
ThisWorkbook.Sheets("Sheet1").Range("H51").Value = _
dsa & asd
ThisWorkbook.Sheets("Sheet1").Range("L51").Select
ActiveSheet.Pictures.Insert (filepath & dsa & ".jpg")
End Sub

 

[bensonsearch]

I would double check the spelling of the file and location. Ensuring the username is the name of file.

Also is it a jpg ? Maybe also try in the code .jpeg I stead

 

[stephenscott]

thanks will do.

some machines are using 2003 & 2010

 

[bensonsearch]

thanks will do.

some machines are using 2003 & 2010

Is only some machines getting the error?

 

[bensonsearch]

I think its the location. each user has there own area for my documents. on windows 7 would be like

C:\Users\nickolas.benson\Documents\

xp from memory is

C:\Documents and Settings\nickolas.benson\My Documents\

please check if this is so

 

[bensonsearch]

I think its the location. each user has there own area for my documents. on windows 7 would be like

C:\Users\nickolas.benson\Documents\

xp from memory is

C:\Documents and Settings\nickolas.benson\My Documents\

please check if this is so

how did you go?