Wednesday 28 October 2015

Creating a sent item in Outlook without sending it

I was asked if it was possible to create an email within Outlook and make it look like it had been sent, without actually ever sending it. This question is very much in the grey area of "Don't ask why?"

After rummaging around on the Internet I couldn't actually find a reliable answer, but I remembered doing something similar with the nickname (.nk2 file) 

1. Open Outlook
2. Click File - Manage Rules & Alters
3. Click Next
4. At the bottom of the window under Start from a blank rule, select 'Apply rule on messages I send'
5. Click Next
6. Tick the box that says 'sent to people or public group' then in the bottom window click on the underlined text 'people or public group'
7. In the To box enter the email address you are sending to then click OK
8. Click Next
9. Tick the box that says 'move a copy to the specified folder' then in the bottom window click on the underlined text 'specified'
10. Select Sent Items and click OK
11. Click Next (twice)
12. Click Finish
13. You will be warned that this rule will only run when Outlook is running, click OK (twice)
14. Either Disable your wireless / Internet connection OR put make Outlook Work Offline.
15. Create a new email and send it.
16. As Outlook is offline the message should stay in your Outbox. Scroll through your folders until you find the Outbox, it should show that 1 message is unread. Click into the Outbox and then delete the email contained within.
17. Navigate to your Sent Items, this should also show 1 unread item which is the email you have just 'sent'
18. If you want to modify the date on a sent item then you can do so by modifying the date / time on your computer. 


I am currently still searching for a simpler solution, and am also looking out for some sort of add-in that may just create the sent object automatically without having to go thorough the steps outlined above

Friday 24 July 2015

Connect Excel to Microsoft SQL Server and query Database

Open Microsoft Visual Basic for Applications (Alt+F11)

Click Tools – References

Add “Microsoft ActiveX Data Objects 2.7 Library”

Create a new module (I tend to keep all my SQL code in a module of its own e.g. mod_SQL)

Then create a new function or sub routine




Function myFunctionName(ByVal s_myString As String) As Integer

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String

'Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=Address;" & _ 
"Initial Catalog=DBName;" & _
"User Id=Username; Password=Password"

'Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

'Open the connection and execute. 
conn.Open sConnString
Set rs = conn.Execute("SQL Statement")

'Check we have data.
If Not rs.EOF Then
myFunctionName = rs("Return Value")
' Close the recordset
rs.Close
Else
myFunctionName= -1
End If

'Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing

End Function

Wednesday 27 May 2015

Word / Office 2013 has stopped working

Annoyingly if you look around the web for Word / Office crashing on start up or "has stopped working" you will find thousands of posts all recommending the same thing; start word in safe mode and then disable COM Addins. 

After trouble shooting the issue for 3 hours and trying a variety of things I finally managed to resolve my problem. It seems my users issue was to do with their USB docking station (and the version of the Display link Adapter software they had installed)

I guess I could try upgrading the version of the software, but the user works in a different country so I don't want start uninstalling anything that may cause me to be disconnected. To get them up and running I was able to disable hardware acceleration for office, to do this

Run Regedit
Go to HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common
Create a new Key called Graphics
Under the new key create a new DWORD called DisableHardwareAcceleration
Set the DWORD value to 1

Tuesday 17 February 2015

Excel VBA adding a comment to a cell

Code below can be used to add comments to a cell. I find this useful for providing errors and feedback to the end user.


Dim o_Range As Range

Set o_Range = Sheet1.Range("A1)

o_Range.ClearComments
   
o_Range.AddComment "Test Comment"