Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Email ? (Excel 2000)

    Good Morning Everyone,

    I will be setting up a spreadsheet that once the user updates will email to specific people. I will be designing this so the user has as little input as possible.

    I think the best way to hand this is create an email sheet which hill hold all the possible email addresses and on the Primary spreadsheet provide the list of names and possibly a check box (or what ever..still considering how to handle this part). Once the user selects a name(s) then the spreadsheet is sent to the respective email addressess.

    At any rate, does anyone have any suggestions on where I might find some information on this?

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email ? (Excel 2000)

    Roberta,

    What email client are you using ? Is it Outlook ?

    Andrew C

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email ? (Excel 2000)

    Andrew,

    Yes...

    Roberta
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email ? (Excel 2000)

    Roberta,

    The following code should attach the active workbook to an OutLook Mail item and send it to the recipients listed passed to it. The list of recipients should be seperated by a semicolon ";".

    For this code to function you will need to set up a reference to the Outlook object in your application. To do this goto the VB editor and select Tools, References.. and scroll down the list until you reach Microsoft Outlook x.x Object Library, (where x is the version number, probably 8 or 9) and click the check box to select it.

    Sub AttachToMail()
    Dim objOLook As New Outlook.Application
    Dim objOMail As MailItem
    Set objOLook = New Outlook.Application
    Set objOMail = objOLook.CreateItem(olMailItem)
    With objOMail
    .To = "Recipient1; Recipient2"
    .CC
    .Subject = "Subject"
    .Body = "BodyText"
    .Attachments.Add ActiveWorkbook.FullName
    .Send
    End With
    Set objOMail = Nothing
    Set objOLook = Nothing
    End Sub

    When you decide the method for selecting the recipients, and if you need further help just post back.

    Andrew

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email ? (Excel 2000)

    Andrew,

    I decided against using a check box because the actual click event will trigger a Positive response. At this point I am going with the following:

    A1=RLN (which should = the email address from the Email list)

    B1=x(if this person will recieve the email, if this person will not receive the email there won't be an "x")

    There will be 6 possible name choices and not always will the same choices be made. (i.e. RLN and DAS might be selected now and CRL and DAS and PDN might be selected the next time)

    As always, Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email ? (Excel 2000)

    Hi Roberta,

    The following code will search a given range for addressees that have been marked according to your scheme, and the dispatch the active workbook to those persons. It assumes that the contents of the range (which is referred to as "MailList") contains valid email addresses. You will need to name your list of names as MailList, or change the code accordingly.

    Sub AttachToMail()
    Dim objOLook As New Outlook.Application
    Dim objOMail As MailItem
    Set objOLook = New Outlook.Application
    Set objOMail = objOLook.CreateItem(olMailItem)
    Dim strMailTo As String
    Range("MailList").Select
    For I = 0 To Selection.Count - 1 ' compose the Address To list
    If ActiveCell.Offset(I, 0).Value > "" And ActiveCell.Offset(I, 1).Value = "x" Then
    strMailTo = strMailTo & ActiveCell.Offset(I, 0).Value & ";"
    Next
    ActiveCell.Select
    With objOMail
    .To = strMailTo ' List of addressees
    .Subject = "Subject" ' The subject matter
    .Body = "BodyText" ' whatever message goes with attachment
    .Attachments.Add ActiveWorkbook.FullName
    .Send ' send the message
    End With
    Set objOMail = Nothing
    Set objOLook = Nothing
    End Sub

    Just to ensure that you do not send the incorrect file, you should attach a button to activate the macro on the actual workbook you do want to send.

    Andrwe C

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email ? (Excel 2000)

    Hi Andrew,

    Well I must be a total dummy....

    I put a button on my worksheet as you said and pasted the code to the button and rem'd the first line of code ( works differently than Access...I would have put the code in the OnClick Event of the button in Access...but Excel doesn't have this option)

    I renamed my mail list worksheet to "MailList"...but when I click on the button, I get the following msg:

    User-defined type not defined with the 1st line of code highlighted...."Dim Obj As New Outlook.Application"

    <img src=/S/igiveup.gif border=0 alt=igiveup width=31 height=23> so sorry to be such a pain in the ... well...u know...

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email ? (Excel 2000)

    Hi Roberta,

    as mentioned earlier (Post 49654) you need
    <hr>to set up a reference to the Outlook object in your application. To do this goto the VB editor and select Tools, References.. and scroll down the list until you reach Microsoft Outlook x.x Object Library, (where x is the version number, probably 8 or 9) and click the check box to select it.<hr>
    Try that and see if it helps.

    Andrew

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email ? (Excel 2000)

    Andrew,

    I did that yesterday but apparently didn't save my changes and had to correct it this a.m.....doing so did get past that error, however, the new error is "Next without For"...with "Next ActiveCell.Select" highlighted.

    Thanks
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email ? (Excel 2000)

    Roberta,

    the code should read as

    If ActiveCell.Offset(I, 0).Value > "" And ActiveCell.Offset(I, 1).Value = "x" Then
    strMailTo = strMailTo & ActiveCell.Offset(I, 0).Value & ";"
    Next
    ActiveCell.Select


    wirh Next and ActiveCell.Select on different lines. Make sure that is the case, and try again.

    Andrew

  11. #11
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Email ? (Excel 2000)

    Andrew,

    That is the way the code reads (see below)

    Next
    ActiveCell.Select
    With objOMail
    .To = strMailTo ' List of addressees
    .Subject = "Subject" ' The subject matter
    .Body = "BodyText" ' whatever message goes with attachment
    .Attachments.Add ActiveWorkbook.FullName
    .Send ' send the message
    End With
    Set objOMail = Nothing
    Set objOLook = Nothing
    End Sub

    But still get the msg: "Next without For"

    Thanks
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Email ? (Excel 2000)

    Roberta, as ActiveCell.Select is not needed, try deleting it and see if it helps.

    I assume the line For i = 0 To Selection.Count - 1 is ok - have a look and see. If the problem persists, post the entire code you are using.

    Andrwe

Posting Permissions

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