Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: any ideas?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    I appreciate the help from everyone at Mr. Excel.

    viper

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Guderup, Denmark
    Posts
    288
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thank you,
    I appreciate the help from everyone at Mr. Excel.

    viper

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •