View Full Version : A Simple Email from Excel
Mavver
04-02-2007, 04:41 AM
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
04-02-2007, 06:01 AM
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
04-02-2007, 06:41 AM
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
04-02-2007, 06:45 AM
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
04-02-2007, 06:58 AM
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
04-02-2007, 07:01 AM
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
04-02-2007, 07:13 AM
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
04-02-2007, 09:01 AM
Do a search for ClickYes on Google.
Charlize
04-03-2007, 05:30 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.