VBA-Excel: User Name and eMail Problem [modified]
-
Hi Using VBA for Excel - Office XP and 2003 We have an file on a network with a production schedule, that is accessed and modifeid by a couple of users. I am using the Workbook's BeforeSave event to prompt the person who is trying to save the Workbook to send an email to other useds advising them of the changes to the production schedule. Two questions: 1) How can I get the current Users name? - Not to worry, I have found this!! 2) When I send the mail, I get a pop-up from Outlook saying that another program is tying to send mail, and you then have to click 'Allow for ? Minutes, and say yes. Is there any way to stop this pop-up? I dont mid if the email appears, and the user must clicjk 'Send', but also could not get the code for that. (I.e in the code below, I go myMailItem.Send - I tried myMailItems.Show, but this does not work. This is the code I am using: Dim response As String response = MsgBox("Would you like to email Andrea advising of the the changes you have just saved?", vbYesNo) If response = 6 Then Dim User As String 'How do I get UserName ????? Dim myOutlook As Object Dim myMailItem As Object Set myOutlook = CreateObject("Outlook.Application") Set myMailItem = myOutlook.createitem(0) myMailItem.Recipients.Add "" myMailItem.Subject = "Production Schedule Changes" myMailItem.body = "Hi Andi" & Chr(13) & Chr(13) & _ "Please see the latest changes in the Production Schedule" _ & Chr(13) & Chr(13) & "Regards" myMailItem.Send Set myOutlook = Nothing Set myMailItem = Nothing End If -- modified at 5:30 Friday 11th May, 2007