Email ? (Excel 2000)

Topic

New Reply

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,

Reply | Quote

Replies

Roberta,

What email client are you using ? Is it Outlook ?

Andrew C

Reply | Quote

Andrew,

Yes…

Roberta

Reply | Quote

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

Reply | Quote

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,

Reply | Quote

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

Reply | Quote

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”

so sorry to be such a pain in the … well…u know…

Thanks,

Reply | Quote

Hi Roberta,

as mentioned earlier (Post 49654) you need [indent]

---

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.

---

[/indent]
Try that and see if it helps.

Andrew

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote