Posts by damianmcl78

Re: Send Email With "do Not Deliver Before" Option

Excellent, many thanks Andy.

Re: Send Email With "do Not Deliver Before" Option

bump

Hello fellow Excelers

I am using the code below as part of a module to send emails. My query is simple, with two parts.

I would like to add in an agrument to set the "Do not deliver before" option in the email. In doing so, how could I code this so that it sets the value to todays date at 1800hrs?

Any and all help is appreciated.

Regards,
Damian.

Set olApp = New Outlook.Application                                 'prepare new instance of Outlook
        Set olNewMail = CreateItem(olMailItem)                              'prepare to create new mail item
        sendTo = strMailcode & rngCell                                      'define recipient
        With olNewMail
            With .Recipients.Add(sendTo)                                    'create new email with recipient
                    .Type = olTo
                    If Not .Resolve Then                                    'check if recipient exists in contacts folder
                    MsgBox "Cannot find recipient: " & sendTo, vbInformation      'recipient does not exist report to user
                    LogEvent "Emailing error - cannot find recipient: " & sendTo  'log the error
                    GoTo Name_Error                                         'continue with next entry
                    End If
            End With
            .Subject = "Switchboard Directory Updates"                      'add email subject
            .Attachments.Add "\\UKSCCI03\xxxxxxxx\Directory Verification Utility\Procedures\PROACTIVE UPDATES - RECIPIENT INSTRUCTIONS.DOC", olByValue, 1, "Instructions"   'add instruction attachment
            .Attachments.Add "C:\TEMP\OUTPUT.XLS", olByValue, 2, sendTo     'add data attachment
            .Body = Chr(11) & Chr(11) & "This E-mail has been sent from xxxxx. Please open the Word Document for instruction. Thank you." & Chr(13) _
                    & Chr(13) & "If you have any queries regarding this e-mail, please dial switchboard on xxx and ask for a member of the Directory Update Team." & Chr(13) & Chr(13) & "If all details within are correct, no response is necessary" _
                    & Chr(13) & Chr(13) & "If your response should be returned as a 'send failure', please route your response to xxxxxx.co.uk"
            .Send                                                           'send email
        End With

Re: Conditional Formatting - Syntax difficulties

I knew it would have been something really damn easyyyy!

Derk, you're a lifesaver. I am going to hit myself with some kind of blunt object for being so thick today!!

Appreciated!

Hi folks

This is really quite an easy one, I guess I am just having an off-day. Allow for a Monday methinks.

Anyway, I always run into problems when using conditional formatting. I would normally look to VBA to support me, but I can't in this case.

I am trying to set a rule whereby (B7 >= B4) OR (B7 = "") then change the format.

I am unable to use the other two conditional format rules, as they are taken up with other rules.

Can anyone help me out here?

Thanks in advance,
Damian

Re: Difficulties in Date Formatting for Combobox Items

bump

I have attached a sample of my data.

Basically, this sample data is the result of a advanded filter capturing the unique records. The resultant list is used to populate a combobox. The code I used for this is as follows:

Private Sub CommandButton1_Click()
Worksheets("Raw Data IN").Range("AA2").Value = ComboBox1.Value
Unload Me
End Sub
Private Sub UserForm_Initialize()


'count up number of populated rows
Dim intCounter, i As Integer


intCounter = 0
 
For Each cell In Worksheets("Raw Data IN").Range("Z2:Z65536")
    If cell.Value <> "" Then
        intCounter = intCounter + 1
    End If
Next cell
 
intCounter = intCounter + 1


With ComboBox1
    .Clear
    For i = intCounter To 2 Step -1
        .AddItem Format(Worksheets("Raw Data IN").Cells(intCounter, 26).Value, "dd/mm/yyyy")
        intCounter = intCounter - 1
    Next i
End With
End Sub

The first problem I encountered was the way the combobox values were displayed. It would format the dropdown as "mm/dd/yyyy" which was confusing to read. I countered this by using the Format function. That seemed fine from there.

However the other problem is that when the desired value has been selected, and the user clicks 'ok', the selected value is written to a cell in a worksheet. This was also being output in the format "mm/dd/yyyy". So I tried to apply the Format function to the output value, and that had no effect whatsoever; it remained in "mm/dd/yyyy" format.

It is important that the date is correct, because at a later point similar script is used to set a second date. Then from the two dates, a range is defined via an autofilter. The bizarre thing was that the first date displayed and treated as "dd/mm/yyyy" format, the second seemed to be displayed as "mm/dd/yyyy" but was treated as a "dd/mm/yyyy" format but was the wrong date as the month became the day, and the day become the month.

I am in danger of writing myself into confusion here. Can anyone assist me with this predicament? I am confounded and beside myself!

All help appreciated!

Regards,
Damian

Re: Delete row based on criteria, with exceptions.

Andy,

Thanks for that solution, it works perfectly and is very fast.

Is the chunking something to do with the speed of its processing?

Much appreciated,
Damian

Hi all

Hope someone can sort me out, because this is taking me far too long to get anywhere.

What I am trying to do is basically delete all the rows of data that contain the following times: 08:30-17:00 (inclusive). However I do not want any of these row deletions to occur on rows pertaining to a Saturday or Sunday; they need to remain entirely intact.

I have uploaded a tiny sample of my data.

The key is really having a coding that executes quickly, as I will be working with 40-50k rows of data.

Any and all help is greatly appreciated!

Thanks in advance,
Damian

Re: VBA to extract first two characters from a string

Thanks Will & Ivan (seeing as you both submitted at the same time with an identical response

This works just grand! I knew it would have been something simple!!!

Regards,
Damian

I am sure this is a simple one, but I'm foxed...

I am working with a stored string in VBA, and I would like to 'capture' the first two characters of that string, and store them to memory as a separate string.

Can anyone help?

Thanks for that Dave, it works perfectly!!

:cheers:

Hi again folks,

A quicky here: Can anyone help me with some VBA that will delete all the rows in my worksheet that are hidden, upon the application of custom autofilter?

Thanks in advance,
Damian

That's a good idea royUK, now why didn't I think of that...oh yeah, I left my brain somewhere else. Probably still tucked up in bed where I wish I was!! Thanks for that

Hi again folks,

Busy again with more data manipulation projects

First off, here is a sample of the data I am working with:

1. OFFERED,External,30/06/04 09:00,64,Wednesday
1. OFFERED,External,30/06/04 09:30,90,Wednesday
1. OFFERED,External,30/06/04 10:30,105,Wednesday
1. OFFERED,External,30/06/04 11:30,91,Wednesday
1. OFFERED,External,30/06/04 12:00,98,Wednesday
1. OFFERED,External,30/06/04 12:30,85,Wednesday

The data source is historic and has new data appeneded onto the end. THis means that the volume of data is very high.

What I would like to do, is have VBA to allow me to input two dates; this will will specify a range of dates. Then I would like to remove all the rows of data that do not fall within that particular range.

Any and all help is greatly appreciated.

Many thanks in advance,
Damian

Ah excellent, thank you jindon, I knew it would be something really simple that I was missing. My head is not screwed on today. I have no idea where I left it.

Hi again folks,

Here's a quickie that I hope somone can shed light upon...

I am trying to have a VBA script enter this formula, Range("E2").Formula = "=Text(C2, "dddd")", into a cell. However, I am having problems regarding the quotation marks. As the compiler spits out an "Expected: end of statement" error.

Can anyone shed light on this one?

Thanks in advance,
Damian

Thanks for you help chaps, I'm going to go with the VBA solution. Many thanks again!!

Hi Folks,

I am hoping someone can assist me with this one. First off, here is a sample of the data I am working with (source file is a .CSV which I import as external data into a worksheet):
1. OFFERED,1417,2004-06-30 09:00:00.000,64
1. OFFERED,1417,2004-06-30 09:30:00.000,90
1. OFFERED,1417,2004-06-30 10:30:00.000,105
1. OFFERED,1417,2004-06-30 11:30:00.000,91
1. OFFERED,1417,2004-06-30 12:00:00.000,98

As you will see with the date/time data, it contains "09:30:00.000". This is what I need to replace using a macro, as it causes problems with a pivot-table that I use later on. I need to reformat to the format "09:30:00" using a VBA script. Changing the cell format does not seem to provide me with the solution.

Can anyone assist me with this?

Thanks in advance,
Damian

Thanks Andy, but this doesnt seem to be working. Should this macro fire as soon as an email hits the inbox??