Posts by xiaowang

Quote from Fluff13

How about

Code

    Range(Range("H2").Value).Offset(, 3).Value = "Text"

Hi Fluff13 excel recognizes it as a text and not as a cell address hence im unable to do that therefore im looking for other alternatives that allows the cell address to be recognized as a cell address.

Hi ozgrid, is there any vba code that i can use to dervice the location from cell H2 and then offsetting it by 3 columns?

Ie. If the contents in cell H2 is $a$3 , i would like to output a text in cell D3? Thank you in advance!

Quote from Kenneth Hobson

Running every 12 hours does not ensure that the email is sent. There are ways to do that if needed. Normally, I leave Outlook open so it is not an issue.

I have upload an example to run a routine on open. It shows a popup option to allow you to abort the run on open if opened manually. Using this method, I put the workbook into a Windows Scheduled run.

Thank you!

Hi may i know how do i run this at certain time intervals lets say every 12 hours? Thanks in advance!

Sub eMail()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList As String
Dim eSubject As String
Dim eBody As String
Dim Sheets As Worksheet
Dim OutApp, OutMail As Object


With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With




lRow = Cells(Rows.Count, 6).End(xlUp).Row


For i = 2 To lRow
  If (Cells(i, 1)) <> "" And CDate(Cells(i, 3)) - Date <= 7 And CDate(Cells(i, 3)) - Date > 0 Then  '<--------------------------------------------- change this range
     Set OutApp = CreateObject("Outlook.Application")
     Set OutMail = OutApp.CreateItem(0)

        toList = Cells(i, 4)    'gets the recipient from col D
        eSubject = "Reminder to return tools on " & Cells(i, 3)
        
        eBody = "Hello " & Cells(i, 1) & "," & vbNewLine & vbNewLine & _
            "Do remebmber to return " & Cells(i, 6) & vbNewLine & vbNewLine & _
            "Sincerely, " & vbNewLine & _
            "Admin"


        On Error Resume Next
        With OutMail
        .To = toList
        .CC = ""
        .BCC = ""
        .Subject = eSubject
        .Body = eBody
        
        .Display   ' ********* Creates draft emails. Comment this out when you are ready
        '.Send     '********** UN-comment this when you  are ready to go live
        
        End With
 
 
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
 Cells(i, 7) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column A"
End If
Next i


ActiveWorkbook.Save


With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
End Sub

Hi ozgrid I’m currently proceeding with an equipment withdrawal system which uses a barcode scanner to scan their office ID. This allows the user to be able to press the check button which uploads their name, department and e-mail address. After the scanning of their office ID, they scan the item they want to loan out. For returns, the process is the same. The data when withdrawing and returning the items is captured into a database in separate sheets called “withdrawal_data” and “ return_data” this is achieved by a user form. One of the main features of this project is to be able to send out an email 3 days before the selected return date. However I do not know how to proceed with this. I have an idea to make the vba program execute a code to send the email out via a clickable button on the sheet. However I have no clue on how to make a program which counter checks against the return list to ensure the item has already been returned which ensures the email will not to be sent out. Some ideas that I have thought of are conditions to be fulfilled in order for the code not be executed the following are stated below.

Conditions

-The user in both the return and withdrawal section is the same

-The borrowers email is the same

-The loan return date is the same

-The item returned and withdraw is the same

Hence all this conditions have to be fulfilled in order for the email not to be sent out. The idea I have thought of is an VBA code which searches through the ‘return’ and ‘withdrawal’ sheet looking for the above conditions and outputting them into a sheet hence generating a sheet which contains the successful returns. In summary , I am able to send out the emails to remind them to return the items however I do not have a way to stop the emails from being sent out despite them already returning the items please help! Below is my excel file feel free to look through to understand it!

Quote from rory

It's basically a way of ensuring that the variables refer to one cell only. It is, as Carim said, a shorthand version of:

Code

Set xRgDate = xRgDate.Cells(1)

Thank you sir your the best!

Quote from Carim

Whenever you are dealing with a range, you might need to get a specific element from the range

Range( i , j ) where i will represents the Row and j represents the Column

You can also omit j ... if you need the first element ... i.e. first row and first column, you can write it : xRgDate(1)

Hope this clarifies

Display More

Oh right..... Got it thank you!

Quote from Carim

I mean ...Set is the method to declare a Range Object ...

For more explanations :

https://www.excel-easy.com/vba/range-object.html

Regarding the entire macro ...

Did you take a look at the link provided ...???

Display More

Yes i do know that set is used to declare a range object however i do not understand the use of the (1) at the back.

Quote from Carim

Hi,

The instruction Set allows to define a Range ...

For the complete explanation, you can refer to :

https://www.extendoffice.com/d…ue-date-has-been-met.html

Hope this will help

Display More

What do u mean by that?

Public Sub CheckAndSendMail()
'Updated by Extendoffice 2018/11/22
    Dim xRgDate As Range
    Dim xRgSend As Range
    Dim xRgText As Range
    Dim xRgDone As Range
    Dim xOutApp As Object
    Dim xMailItem As Object
    Dim xLastRow As Long
    Dim vbCrLf As String
    Dim xMailBody As String
    Dim xRgDateVal As String
    Dim xRgSendVal As String
    Dim xMailSubject As String
    Dim i As Long
    On Error Resume Next
    Set xRgDate = Application.InputBox("Please select the due date column:", "KuTools For Excel", , , , , , 8)
    If xRgDate Is Nothing Then Exit Sub
    Set xRgSend = Application.InputBox("Please select the recipients?email column:", "KuTools For Excel", , , , , , 8)
    If xRgSend Is Nothing Then Exit Sub
    Set xRgText = Application.InputBox("Select the column with reminded content in your email:", "KuTools For Excel", , , , , , 8)
    If xRgText Is Nothing Then Exit Sub
    xLastRow = xRgDate.Rows.count
    Set xRgDate = xRgDate(1)
    Set xRgSend = xRgSend(1)
    Set xRgText = xRgText(1)
    Set xOutApp = CreateObject("Outlook.Application")
    For i = 1 To xLastRow
        xRgDateVal = ""
        xRgDateVal = xRgDate.Offset(i - 1).Value
        If xRgDateVal <> "" Then
        If CDate(xRgDateVal) - Date <= 7 And CDate(xRgDateVal) - Date > 0 Then
            xRgSendVal = xRgSend.Offset(i - 1).Value
            xMailSubject = xRgText.Offset(i - 1).Value & " on " & xRgDateVal
            vbCrLf = "<br><br>"
            xMailBody = "<HTML><BODY>"
            xMailBody = xMailBody & "Dear " & xRgSendVal & vbCrLf
            xMailBody = xMailBody & "Text : " & xRgText.Offset(i - 1).Value & vbCrLf
            xMailBody = xMailBody & "</BODY></HTML>"
            Set xMailItem = xOutApp.CreateItem(0)
            With xMailItem
                .Subject = xMailSubject
                .To = xRgSendVal
                .HTMLBody = xMailBody
                .Display
                '.Send
            End With
            Set xMailItem = Nothing
        End If
    End If
    Next
    Set xOutApp = Nothing
End Sub

Hi could someone please explain to me what does the following do? Thank you in advance!

-Set xRgDate = xRgDate(1)    -Set xRgSend = xRgSend(1)    -Set xRgText = xRgText(1) -xRgDateVal = xRgDate.Offset(i - 1).Value -vbCrLf = "<br><br>" 

Hi RoyUk i have tried keying in a simple VBA program and it works with the run button not greyed out and i have no idea why please help![ATTACH=JSON]{"data-align":"none","data-size":"full","title":"2019-11-12 09_16_52-Microsoft Visual Basic for Applications - Book1 - [Module2 (Code)].png","data-attachmentid":1226109}[/ATTACH]

Hi royUk i have added it as a module and have also added it into the sheet1 however when i press run the dialog box has the run button still greyed out only the cancel button is available. Do advise thanks![ATTACH=JSON]{"data-align":"none","data-size":"full","title":"2019-11-12 08_52_51-Microsoft Visual Basic for Applications - Book1 - [Sheet1 (Code)].png","data-attachmentid":1226107}[/ATTACH]

Hi all im new to VBA here. I'm not sure why when i try to run the macro it only has the cancel function and all other functions are greyed out please advise!!!
[VBA]Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
End With[/VBA]
Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
With Target.Offset(0, 1)
.Value = Now
.NumberFormat = "MM/DD/YYYY hh:mm AM/PM"
End With

End Sub