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.

 

[stephenscott]

I think I am there !

The hyperling is not functional though (does not open outlook?)

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").Formula = "=HYPERLINK(""" & dsa & asd & """)"
ThisWorkbook.Sheets("Sheet1").Range("L35").Select
With ActiveSheet.Pictures.Insert(filepath & dsa & ".jpg")
.Left = Range("L35").Left
.Top = Range("L35").Top
End With
End Sub

 

[bensonsearch]

sorry didnt ever click it :P

ThisWorkbook.Sheets("Sheet1").Range("H51").Formula = "=HYPERLINK(""" & "mailto:" & dsa & asd & """)"

this works fine now

 

[stephenscott]

Thanks, Works fine

The cell reference points to L35 when the macro is complete

Can I reset the cell reference to start at my first cell A12 after the Macro runs ?

 

[bensonsearch]

yeah just change the below again to be what cell you need

ThisWorkbook.Sheets("Sheet1").Range("A12").Select

 

[stephenscott]

Perfect
Thanks Again for all your assistance, Cheers from Canada

 

[bensonsearch]

Perfect
Thanks Again for all your assistance, Cheers from Canada

not a problem as always. Happy coding from Australia

 

[stephenscott]

Now I am getting "really tired".
My Image is sized correctlyand there is no need to position to fit, however but instead of Left Position I want Right Postion on the Cell.
Changed the command below from Left to Right and got

Run-time error '438':
Object does not support Property or Method

Again my logic failed ! any ideas?

Private Sub Workbook_Open()
Dim asd As Variant
Dim dsa As String
Dim filepath As String
filepath = "C:\Documents and Settings\stephen.imrie\"
asd = "@frankcowan.com"
dsa = (Environ$("Username"))
ThisWorkbook.Sheets("Certificate").Range("J60").Formula = "=HYPERLINK(""" & "mailto:" & dsa & asd & """)"
ThisWorkbook.Sheets("Certificate").Range("N57").Select
With ActiveSheet.Pictures.Insert(filepath & dsa & ".jpg")
.Right = Range("L57").Right
.Top = Range("L57").Top
ThisWorkbook.Sheets("Certificate").Range("A12").Select
End With
End Sub

 

[bensonsearch]

thats because there is no such thing as a right property. you need to work from left or top.

try using .width and .height