Excel Email

[jgthtrotih]

Can Excel be set to email you?

Thanks!

 

[L. Howard]

Yes, take a look here.

Example Code for sending mail from Excel

Regards,
Howard

 

[jgthtrotih]

Hi L.

thank you for your reply, im not sure those will work in Excel 2003.. an i am not seeing what i want to do.. i should have explained my self in the first post.. i wanted excel to email me when a count down timer is within 24hrs of reaching 0.

Thanks!

 

[L. Howard]

I found this and many more at the site.
The third code down sends a small message.
You can Google a count down timer to install in the code to make it fire.
Note the red text for info.

Howard

Sub Mail_workbook_1()
'Working in 97-2010
    Dim wb As Workbook
    Dim I As Long
    Set wb = ActiveWorkbook
    If Val(Application.Version) >= 12 Then
        If wb.FileFormat = 51 And wb.HasVBProject = True Then
            MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _
                   "Save the file first as xlsm and then try the macro again.", vbInformation
            Exit Sub
        End If
    End If
    On Error Resume Next
    For I = 1 To 3
        wb.SendMail "", _
                    "This is the Subject line"
        If Err.Number = 0 Then Exit For
    Next I
    On Error GoTo 0
End Sub

Sub Mail_Workbook_2()
'Working in 2000-2010
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim I As Long
    Set wb1 = ActiveWorkbook
    If Val(Application.Version) >= 12 Then
        If wb1.FileFormat = 51 And wb1.HasVBProject = True Then
            MsgBox "There is VBA code in this xlsx file, there will be no VBA code in the file you send." & vbNewLine & _
                   "Save the file first as xlsm and then try the macro again.", vbInformation
            Exit Sub
        End If
    End If
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    'Make a copy of the file/Open it/Mail it/Delete it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))
    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
    Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)
    With wb2
        On Error Resume Next
        For I = 1 To 3
            .SendMail "", _
                      "This is the Subject line"
            If Err.Number = 0 Then Exit For
        Next I
        On Error GoTo 0
        .Close SaveChanges:=False
    End With
    'Delete the file
    Kill TempFilePath & TempFileName & FileExtStr
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub


'The code on this page is only working with Outlook and not with Outlook Express or Windows Mail.
'The following subroutine sends a small text in an e-mail message.
' Change the mail address and subject in the macro before you run it.

Sub Mail_small_Text_Outlook()
'Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    strbody = "Hi there" & vbNewLine & vbNewLine & _
              "This is line 1" & vbNewLine & _
              "This is line 2" & vbNewLine & _
              "This is line 3" & vbNewLine & _
              "This is line 4"
    On Error Resume Next
    With OutMail
        .To = "ron@debruin.nl"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[L. Howard]

Try this for a count down timer.
Paste in a Module.

When setting count down:
2:30 is two hours and thirty minutes; "0:2:30" is two minutes and thirty seconds.

To start count down run "Sub Countup()"

Option Explicit

Sub Countup()
 
Dim CountDown As Date
 
CountDown = Now + TimeValue("00:00:01")
 
Application.OnTime CountDown, "Realcount"
 
End Sub

Sub Realcount()
 
Dim count As Range
 
' *** Format cell E1, or cell of your choice, to: h:mm:ss ***
' *** If other than E1 on sheet, then change [E1] here to match sheet cell choice. ***
Set count = [E1]
 
count.Value = count.Value - TimeSerial(0, 0, 1)
 
If count <= 0 Then
 
'*** call a macro here to send mail from Excel ***
MsgBox "Countdown complete."

Exit Sub
 
End If
 
Call Countup
 
End Sub

Regards,
Howard

 

[jgthtrotih]

Hi L.

Could you help me set this up an give an example of how it runs/works.. as i am not very good with VBA.. i am more of a formula type person..

Thanks!

 

[L. Howard]

Take a look here. Download and give it a try.

https://www.dropbox.com/s/q5aff23ez8wpw7e/Mail%20from%20Excel.xlsm

On the worksheet in E1 you will enter an amount of time in Hours, Minutes & Seconds. There are a couple of examples on the sheet of what the times should look like. For your tests I suggest just entering 10 seconds to get on with the testing in a short time.

As you look over the code for the timer you will notice that to start the timer counting DOWN you will run the Sub named CountUp but it will call the next code and that will run the counter on the sheet DOWN. You can change the sub name to Start_Timer if it makes more sense to you.

You will need to change the email address in the code (and subject title if you want). Scroll pretty much all the way down and see my email address in blue underscore. Enter yours for test purposes.

The mail code is from Ron Debruin's site. It mails a RANGE from a sheet if I understand correctly. It is set now at Range("A1:B5") for the testing I was doing. I just entered some text in those cell for testing.

HOWEVER....! In my tests all works well except when it comes to actually mailing the range to myself. I get an error from my mail provider saying It is trying to send an email to me and options of Yes Send, No Don't Send & Cancel.

I have a post in Mr Excel hoping someone can maybe bail me out. Notwithstanding that I have had some problems with my email when I try to send attachments, so I'm hoping it works for you. That would be encouraging to me.

Question after viewing the download, post back here or off site personal.

Howard

 

[L. Howard]

Take a look here. Download and give it a try.

https://www.dropbox.com/s/q5aff23ez8wpw7e/Mail from Excel.xlsm

On the worksheet in E1 you will enter an amount of time in Hours, Minutes & Seconds. There are a couple of examples on the sheet of what the times should look like. For your tests I suggest just entering 10 seconds to get on with the testing in a short time.

As you look over the code for the timer you will notice that to start the timer counting DOWN you will run the Sub named CountUp but it will call the next code and that will run the counter on the sheet DOWN. You can change the sub name to Start_Timer if it makes more sense to you.

You will need to change the email address in the code (and subject title if you want). Scroll pretty much all the way down and see my email address in blue underscore. Enter yours for test purposes.

The mail code is from Ron Debruin's site. It mails a RANGE from a sheet if I understand correctly. It is set now at Range("A1:B5") for the testing I was doing. I just entered some text in those cell for testing.

HOWEVER....! In my tests all works well except when it comes to actually mailing the range to myself. I get an error from my mail provider saying It is trying to send an email to me and options of Yes Send, No Don't Send & Cancel.

I have a post in Mr Excel hoping someone can maybe bail me out. Notwithstanding that I have had some problems with my email when I try to send attachments, so I'm hoping it works for you. That would be encouraging to me.

Question after viewing the download, post back here or off site personal.

Howard

 

[L. Howard]

Quick follow up, the working code is in a Module not the sheet code vb editor. The stuff in the sheet code vb editor are instructions and the Sub's names are appended with "XX".

Deal with the code in the Module only.

Howard

 

[jgthtrotih]

Hi L.

When i click your link i get lead to a page that comes back with:
[h=3]Nothing Here[/h][h=4]The file you are looking for has been deleted or moved.[/h]
Thanks!