any ideas?

viper

Active Member
Joined
Feb 15, 2002
Messages
382
Right now I am using this code to copy a page, e-mail it, then delete the copy.

What I've run into is that the receiver of the e-mail will not be the same. So, what I'm wanting to do is have a cell contain the name of person that will receive the e-mail and then when the command button is clicked it will send it to that name.

Here's the code:
Sub SendIt()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheet1.Copy
ActiveWorkbook.SaveAs Filename:="Purchase Journal.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=True, CreateBackup:=False
Application.Dialogs(xlDialogSendMail).Show _
arg1:="email.address", _
arg2:="subject"
CloseWorkbook
DeleteFile
End Sub

In arg1 I can specify who receives the e-mail. How can I change it to pick the name from the cell? Another thing is the e-mail address will be the first letter of the first name and ony 7 letters of the last name. ie. if the cell contains the name Fred Bruemmer, the e-mail address would be fbruemme@address.com. We use Lotus Notes as for our e-mail, can I access the address book somehow?

Thanks,
viper
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Assuming the name is in A1 this change to your code should be enough.
(if you have XL2000)

Sub SendIt()
N = LCase(Range("a1").Value)
EMA = Left(N, 1) & Mid(N, InStrRev(N, " ") + 1, 7) & "@address.com"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets(1).Copy
ActiveWorkbook.SaveAs Filename:="Purchase Journal.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=True, CreateBackup:=False
Application.Dialogs(xlDialogSendMail).Show _
arg1:=EMA, _
arg2:="subject"
CloseWorkbook
DeleteFile
End Sub


Regards Tommy
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top