A Simple Email from Excel

[Mavver]

Hello Everyone

I have been self teaching myself VBA in Excel and Access for the last three months hence I have got the very basics but I am not very good at the min, but I use forums to start me off down a road to find the answer to an issue that I need and hence add to the knowledge base.

Todays current issue is sending a generic email via Microsoft Excel.

I have a number of spreadsheets which are owned by different individuals, and at the click of a button the individuals data is appended to a Master spreadsheet. What I would like is some very simple code that will fire an email off to me to let me know that someone has appended their data to my master workbook.

I have something kinda the same in Access already set up, but would like to do something similar in Excel.

Access Code
DoCmd.SendObject acSendNoObject, "", "", "", "", "", "", Me.Response, True, ""

I dont need any workbooks or ranges or anything at all attaching to the email, just a simple text message saying something like "Spreadsheet Updated"

Thanks in advance for any and all help

Regards

Mav

[Mavver]

UPDATE

I found this

ActiveWorkbook.SendMail "mavver@mavver.com", "Enquiry Database Updated"

Which would do the trick, apart from two things

1) It actually attaches the spreadsheet which I dont want, I just want to send the email.
2) It prompts the user to accept this (I can live with this, but was rather it wasnt there)

Ta

Mav

[lucas]

Could you post the entire code that sends the email because somewhere in it there is a call to include the attachment and thats what we need to see.

[Mavver]

Steve,

To be honest that is the only line in the code that I use to send the email.

I think that this specific part of ActiveWorkbook.SendMail is what attaches the spreadsheet. So I cant be more specific but this is truely all I have got in there to send the email.

Mav

[feathers212]

If you are using Outlook, take a look at this code:


Private Sub Email_No_Attachment()

Dim oApp As Object
Dim oMail As Object

'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = "mavver@mavver.com"
.Subject = "Enquiry Database Updated"
.Body = "Master Spreadsheet Updated"
.Display
'.Send rather than .Display will automatically send the email
End With

Set oMail = Nothing
Set oApp = Nothing

End Sub

[lucas]

Try this Mavver:
Option Explicit
Sub SendMailMessage2()
Dim objOLapp As Object
Dim objMailItem As Object
Dim strBody As String

Set objOLapp = CreateObject("Outlook.Application")
Set objMailItem = objOLapp.CreateItem(0) '0=mailitem

strBody = strBody & "Hello," & vbCrLf & vbCrLf
strBody = strBody & "Can anyone tell me how to write a short email" & vbCrLf & vbCrLf
strBody = strBody & "yours," & vbCrLf & vbCrLf
strBody = strBody & "Ed" & vbCrLf & vbCrLf
With objMailItem
.To = "me@aol.com"
.Subject = "subject text"
.Body = strBody
' .Attachments.Add "C:\TEMP\testattachment.txt"
.Display 'show the mail
' .Send 'send the mail = OMG!
End With

End Sub

[Mavver]

Feathers and Steve

Thank you ever so much for this, it works perfectly apart from the security alert that pops up saying do you want to send it since it might be a virus which is still there. I can live with that however....but if anyway knows how to turn it off that would be nice

Ta for the speedy and very helpful......help

Mav

[mdmackillop]

Do a search for ClickYes on Google.

[Charlize]

I use this little thing. No security warnings anymore. Disadvantage is that there isn't a proove of a send mail. So you have to BCC to yourself. The version here provided works for a private configuration behind a router. If you are on a network server you have to uncomment several lines and fill in the network adres of your exchange mail server.Sub Mail_with_CDO()
Dim iMsg As Object
Dim iConf As Object

'Dim Flds As Variant

Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

'If you get an error with the sending argument you have to remove the ' of
'Dim Flds As Variant and the coding between the ***
'************
'iConf.Load -1 ' CDO Source Defaults
'Set Flds = iConf.Fields
'With Flds
' .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
' "exchange network server name"
' .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
' .Update
'End With
'************
'Send an email
With iMsg
Set .Configuration = iConf
.To = "The_one_that_you_want_to_receive@provider.com"
.From = """Charlize"" <your real adress@your_provider.com>"
.Subject = "Info for spreadsheet is updated !"
'Don't remove TextBody. The attachments can not be opened when received.
'Bug in CDO
.TextBody = "Necessary steps are done to update your spreadsheet."
'.addattachment
.Send
End With
Set iMsg = Nothing
Set iConf = Nothing
End SubCharlize
ps. Your internet connection must be available of course.