Posts by Logit
-
-
-
You can clear the error first, then call the function. However, this is an unusual thing to do. I'm following to see how you make out.
Methods
With Err Object there are two methods that you can also use.
- Err.Clear: This method clears the error number and error description from VBA’s memory (It’s different from “On Error GoTo -1” as it doesn’t completely reset the error).
-
-
I understand that Open Office will run an Excel spreadsheet. Open Office is free.
-
There is nothing in your existing macro to cause the issues you are experiencing.
Two things I would try ....
#1: Add another command at the end of your macro, telling Excel to close the first workbook. Redundant I know but ???
#2: Don't utilize the "Workbook_BeforeClose(Cancel As Boolean)". Create a new macro called "CloseMyWB" or something and
place all your macro commands in there ... including closing the first workbook.
-
The following accomplishes the goal you seek. However, it is not presently edited to match your precise layout.
By changing only a few specifics in the code you will have your project complete. Or ... use the attached as is.
Cheers.
Code
Display MoreOption Explicit Sub Send_Email() Dim c As Range Dim strBody As String Dim OutLookApp As Object Dim OutLookMailItem As Object Dim i As Integer On Error Resume Next For Each c In Range("G2:G100") strBody = "Greetings : " & c.Offset(0, -6).Value & "<br></br><br></br>" _ & c.Offset(0, -2).Value & "<br></br><br></br><br></br>" _ & "Sincerely, " & "<br></br><br></br>" _ & "Your Signature Here" If c.Value <> "" Then Set OutLookApp = CreateObject("Outlook.application") Set OutLookMailItem = OutLookApp.CreateItem(0) With OutLookMailItem .To = c.Offset(0, -5).Value .CC = c.Offset(0, -4).Value .Subject = c.Offset(0, -3).Value .HTMLBody = strBody 'c.Offset(0, -2).Value .Attachments.Add c.Offset(0, -1).Value .Display '.Send End With End If Next c End Sub Sub clrSend() Range("G2:G100").Value = "" End Sub
-
Here are two macros ... one to hide and one to unhide. Change the column indicated in the macro code ...
Code
Display MoreOption Explicit Sub HideCols() Dim ws As Integer Dim sCol As String On Error Resume Next sCol = "P:U" For ws = 1 To ThisWorkbook.Worksheets.Count ThisWorkbook.Sheets(ws).Columns(sCol).Hidden = True Next ws On Error GoTo 0 End Sub Sub unHideCols() Dim ws As Integer Dim sCol As String On Error Resume Next sCol = "P:U" For ws = 1 To ThisWorkbook.Worksheets.Count ThisWorkbook.Sheets(ws).Columns(sCol).Hidden = False Next ws On Error GoTo 0 End Sub
-
Did some reearch ... is Outlook open and running in background when you send the emails ?
-
Glad it worked for you as well.
Cheers.
-
Compare the code line by line. I commented out a few lines and corrected the TIME to "00:00:02" where noted.
I ran the edited code without error and it consistently produce a copy of my desktop 10 times in a row ... whereas
your original code would error out on the 3rd or 4th attempt.
-
-
'The following seems to work consistently here :
Code
Display MoreOption Explicit Sub autoCpy() Dim OutApp As Object Dim OutMail As Object Dim strbody As String Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) Call PrintTheScreen Application.Wait (Now + TimeValue("00:00:02")) strbody = "screen shot of your screen" On Error Resume Next With OutMail .to = "[email protected]" .cc = "" .BCC = "" .Subject = "Updated Unit Training Tracker" .Body = strbody .Display SendKeys "^v", True 'DoEvents Application.Wait (Now + TimeValue("00:00:02")) '.send End With 'On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing End Sub Sub PrintTheScreen() Application.SendKeys "(%{1068})" 'DoEvents End Sub
I suspect the primary edit involved the wait time.
-
Code
Option Explicit Private Sub Workbook_Open() Dim DateDueCol As Range Dim DateDue As Range Dim NotificationMsg As String Set DateDueCol = Sheet1.Range("F7:F100") 'the range of cells that contain your due dates For Each DateDue In DateDueCol
I tried to highlight the change in your code but it appears that did not work as desired.
The sixth line down (begins with Set DateDueCol) .... insert the appropriate sheet name before the work Range.
-
You are welcome.
-
-
What is the significance of first selecting I9 ?
-
I wonder if a SELECT CASE would work ?
-
Have you looked in the OUTLOOK / SEND folder to see if the email was actually sent ? Maybe some of the emails are still sittin in OUTLOOK ?
If you believe the issue lies with the number of emails attempting to be sent ... you might try setting a pause between each email sent to see if that would
make a difference (however I doubt that would change anything).
-
Here is another approach :
Code
Display MoreOption Explicit Private Sub CommandButton1_Click() CommandButton1.Visible = False CommandButton1.Enabled = False CommandButton2.Visible = True CommandButton2.Enabled = True 'Your macro code here End Sub Private Sub CommandButton2_Click() CommandButton1.Visible = True CommandButton1.Enabled = True CommandButton2.Visible = False CommandButton2.Enabled = False 'Your macro code here End Sub