Posts by Logit

Logit · Tuesday, 11:49 pm

This will delete EXTRA spaces. Of did you want to delete ALL spaces ?

Code

Sub DelSpcsInCol()
Dim rng As Range

Set rng = ActiveSheet.Range("A1:A10")
    rng.Value = Application.Trim(rng)
End Sub

Logit · Jun 3rd 2022

Try :

Code

ActiveSheet.UsedRange.AutoFilter Field:=6, Criteria1:=

Logit · Apr 28th 2022

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).

Code

Option Explicit

Sub ErrorTest()
OnError:      ErrorCommand

'All of your macro code here ....

ErrorCommand:
    Err.Clear
    ErrorTest
    
End Sub

Display More

Logit · Apr 28th 2022

You could use the GoTo command .... or .... you could simply recalll the Function.

Something like :

Code

Option Explicit

Sub ErrorTest()
OnError:      ErrorCommand

'All of your macro code here ....

ErrorCommand:

    ErrorTest
    
End Sub

Display More

Logit · Apr 26th 2022

I understand that Open Office will run an Excel spreadsheet. Open Office is free.

Logit · Apr 13th 2022

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.

Logit · Apr 12th 2022

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

Option 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

Display More

Logit · Mar 1st 2022

Here are two macros ... one to hide and one to unhide. Change the column indicated in the macro code ...

Code

Option 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

Display More

Logit · Feb 9th 2022

Did some reearch ... is Outlook open and running in background when you send the emails ?

Logit · Feb 1st 2022

Glad it worked for you as well.

Cheers.

Logit · Jan 30th 2022

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.

Logit · Jan 30th 2022

When using the ActiveX button, you need to insert the name of the macro you want to run ... like this :

Code

Private Sub CommandButton1_Click()
     GenerateColors
End Sub

Logit · Jan 30th 2022

'The following seems to work consistently here :

Code

Option 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

Display More

I suspect the primary edit involved the wait time.

Logit · Jan 18th 2022

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.

Logit · Jan 17th 2022

You are welcome.

Logit · Jan 17th 2022

Code

Option Explicit

Sub InputBoxEntry()
Dim DataVal As String
    DataVal = InputBox("Enter Value ...", "Data Entry")
    ActiveCell.Offset(0, 27) = DataVal
End Sub

Logit · Jan 16th 2022

What is the significance of first selecting I9 ?

Logit · Dec 25th 2021

I wonder if a SELECT CASE would work ?

Logit · Dec 21st 2021

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).

Logit · Dec 9th 2021

Here is another approach :

Code

Option 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

Display More

Posts by Logit

Code to remove spaces from cells

Help for code

On Error, Restart Function

Methods

On Error, Restart Function

Stand alone sheet

Open another workbook on current workbook beforeclose event

Email Individual or Selected Individuals Using VBA/Macro Actions

SuperHide column (not exactly)

Sending several emails at a time that arrive days later...

Need help with screen capture and outlook VBA script

Need help with screen capture and outlook VBA script

Macro to Color Fill Cell based on Cell Hex Code Value.

Need help with screen capture and outlook VBA script

How do I run my macro only on one specific sheet?

Call input box by command button for Data entry.

Call input box by command button for Data entry.

Call input box by command button for Data entry.

Email based on conditions

Excel VBA sends ~50 emails sequentially via Outlook; but some not rec'd

Hide / Unhide Buttons with the help of a code