Solved: Email function on spreadsheet

[cavemonkey]

Hi

I need to write a code which needs me to send emails to respective personnel in the company when the sheet have finished updating. However, I have no idea of how to start.

Please help. Thanks

[lynnnow]

Hi,

I had a similar problem, I looked up this site for help, you need to dig a bit for the exact solution and mix and match a bit. This is what I use though as my solution for the query you have.


ActiveWorkbook.SendMail Recipients:="Reports", Subject:=Mid(UserFile.Name, 1, Len(UserFile.Name) - 4)


In the example cited above, "Reports" is a group of people so I've just used the group ID and the "Subject" line is long because I pick the file name and remove the extension part.

This is what you can do for the recepients part. If you are using MS Outlook 2003 then export the address book to a csv and use the 4th and 6th columns in a userform with a ComboBox to select the receipient and use that value to send your mail to. I've just got this idea, I'll try it out and let you know the results.

Till then you can try something on these lines and wait and hope.

Lynnnow

[lynnnow]

Hi,

After some testing this is something you can do...

Put this part in the modules part

Sub fiddle()
Doodle.Show
End Sub


This section has to be put in the userform code section:

Private Sub CommandButton1_Click()
sendmailquestion = MsgBox("This file will be sent to " & ComboBox1.Value, vbYesNo)

If sendmailquestion = 6 Then
ActiveWorkbook.SendMail Recipients:=ComboBox1.Value, Subject:=Mid(ActiveWorkbook.Name, 1, Len(ActiveWorkbook.Name) - 4)
Else
Unload Me
Exit Sub
End If
Unload Me
End Sub

Private Sub UserForm_Initialize()

Frame1.Caption = "Trial"

Workbooks.Open ("C:\TrialCode\OE_Contacts1.csv")
Range("F2").Activate
i = 0

Do While Not IsEmpty(ActiveCell.Value)
DoodleCombo(i) = ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
i = i + 1
Loop

ActiveWorkbook.Close False

ComboBox1.List() = DoodleCombo

End Sub


If your mail program is set to block other programs sending mail, you will be prompted with an error msg that another program is sending mail, wait for the "Yes" button to get activated and then you can send your mail.

This is not a very stellar example, but it works the way I want it. You need to export your OE/MS Outlook contacts to a csv file so it picks up the ids from there.

HTH

There is much smarter way to go around all this code, but I'm still learning and this is how I could solve it.

lynnnow

[cavemonkey]

Thanks.

I found a code somewhere on the email thing also


Sub sendmail()
Dim OutlookApp As Object
Set OutlookApp = CreateObject("Outlook.Application")
With OutlookApp.CreateItem(olMailItem)
.Subject = "MOS List"
.Body = "This is to inform you that the MOS List has been updated. You can view it at: "website link"
.To = "email add."
.Send
End With
'Application.ScreenUpdating = False
End Sub


This works however if only you have one person to email to. I'm still trying to figure out how to add more email add into so that I can send it out to people that needs to be informed.

I would appreciate if someone can enlighten me as to how to go about from here.

Thanks.

[lynnnow]

Hi,

Good you found something. You can still use the example I gave you for the userform part. All you have to do is replace the ".To" field set to the variable instead of the fixed value. I tried it out, but the code didn't send out any mail. Need to dig a bit more and see what I'm missing :dunno