outlook 2007 automation

Thanks a ton will check and update ...

Hey I am gettin :
Runtime error # 13 type mismatch !! any ideas on this.I am an absolute newbee at VBA

Couple of things You missed here :
1/ I want my signature to be on the e mail.
2/ declaring Newline as a variable .. if I write it as :

Dim NewLine as Long

Will that be correct ??
Chao !!

try

Please Login or Register  to view this content.

Still the Same error "Run time Error 13 -Type Mismatch"...any clue

ill try it when i get home, i have outlook there. my work computer does not. ill see what i can do!

Originally Posted by GaidenFocus

ill try it when i get home, i have outlook there. my work computer does not. ill see what i can do!

@ thankls awaiting your respponse

Originally Posted by broro183

hi,

I think you can remove the "Dim Newline as string" & add "vb" to the front of "newline" where it is used (ie "vbnewline"). vbnewline is a known/native vba constant for ascii (?) code number 13, which is a type of line break.

Edit: what line of code is highlighted if you click debug when the error message pops up? /edit

hth
Rob

I have new set of queries now .
1/ Edit Following code.in line with the my uploaded file (attachment )
2/ How to combine two functions to act as one
I am posting two procedure and functions which will help ,if corrected of course ,in achieving the desired goal ,.If you can edit /correct /rectify these i will be great full.

Procedures:

Sub Send_Row_Or_Rows_2()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim Ash As Worksheet
Dim Cws As Worksheet
Dim Rcount As Long
Dim Rnum As Long
Dim FilterRange As Range
Dim FieldNum As Integer
Dim strBody As String
Dim SigString As String
Dim Signature As String


On Error GoTo cleanup

Set OutApp = CreateObject("Outlook.Application")

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

'Set filter sheet, you can also use Sheets("MySheet")
Set Ash = ActiveSheet

'Set filter range and filter column (column with e-mail addresses)
Set FilterRange = Ash.Range("A1:H" & Ash.Rows.Count)
FieldNum = 2 'Filter column = B because the filter range start in column A

'Add a worksheet for the unique list and copy the unique list in A1
Set Cws = Worksheets.Add
FilterRange.Columns(FieldNum).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Cws.Range("A1"), _
CriteriaRange:="", Unique:=True

'Count of the unique values + the header cell
Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))

'If there are unique values start the loop
If Rcount >= 2 Then
For Rnum = 2 To Rcount

'Filter the FilterRange on the FieldNum column
FilterRange.AutoFilter Field:=FieldNum, _
Criteria1:=Cws.Cells(Rnum, 1).Value

'If the unique value is a mail addres create a mail
If Cws.Cells(Rnum, 1).Value Like "?*@?*.?*" Then

With Ash.AutoFilter.Range
On Error Resume Next
Set rng = Selection.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If rng Is Nothing Then
MsgBox "The selection is not a range or the sheet is protected. " & _
vbNewLine & "Please correct and try again.", vbOKOnly
Exit Sub
End If

With Application
.EnableEvents = False
.ScreenUpdating = False


End With
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = Cws.Cells(Rnum, 1).Value
.CC = ""
.BCC = ""
.Subject = "Test mail"
strBody = "<H3><B>Dear Customer</B></H3>" & _
"Please visit this website to download the new version.<br>" & _
"Let me know if you have problems.<br>" & _
"<A HREF=""http://www.rondebruin.nl/tips.htm"">Ron's Excel Page</A>" & _
"<br><br><B>Thank you</B>"
.HTMLBody = strBody & RangetoHTML(rng)
.Display 'Or use Send
End With
On Error GoTo 0


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

Set OutMail = Nothing
Set OutApp = Nothing
End With

End If
Set OutMail = Nothing
'End If

'Close AutoFilter
Ash.AutoFilterMode = False

Next Rnum
End If

cleanup:
Set OutApp = Nothing
Application.DisplayAlerts = False
Cws.Delete
Application.DisplayAlerts = True

With Application
.EnableEvents = True
.ScreenUpdating = True
End With
SigString = "C:\Users\" & Environ("Alok") & "\AppData\Roaming\Microsoft\Signatures\Alok.htm"

If Dir(SigString) <> "" Then
'Signature = GetBoiler(SigString)
Else
Signature = ""
End If

End Sub

2] Procedure:

'To use the code that is presented in this section, first create a table on the active sheet with the following columns:

'* Column A: Names of the people

'* Column B: E-mail addresses

'* Column C: yes or no ( if the value is yes, an e-mail message is created)

'The following subroutine loops through each row on the active sheet and, if there is an e-mail address in column B and Yes in column C, an e-mail message for each person listed in column A is created that has a reminder



Sub Create_Mail_From_List()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, and Outlook 2010.
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "yes" Then

Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Reminder"
.Body = "Dear " & Cells(cell.Row, "A").Value _
& vbNewLine & vbNewLine & _
"Please contact us to discuss bringing " & _
"your account up to date"
'You can also add files like this:
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display.
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next cell

cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub


3// Functions:
Option Explicit

Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2010
Dim fso As Object
Dim ts As Object
Dim TempFile As String
Dim TempWB As Workbook
'Dim GetBoiler 'ByVal_
'Dim sFile As String 'As String

TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"

'Copy the range and create a new workbook to past the data in
rng.Copy
Set TempWB = Workbooks.Add(1)
With TempWB.Sheets(1)
.Cells(1).PasteSpecial Paste:=8
.Cells(1).PasteSpecial xlPasteValues, , False, False
.Cells(1).PasteSpecial xlPasteFormats, , False, False
.Cells(1).Select
Application.CutCopyMode = False
On Error Resume Next
.DrawingObjects.Visible = True
.DrawingObjects.Delete
On Error GoTo 0
End With

'Publish the sheet to a htm file
With TempWB.PublishObjects.Add( _
SourceType:=xlSourceRange, _
Filename:=TempFile, _
Sheet:=TempWB.Sheets(1).Name, _
Source:=TempWB.Sheets(1).UsedRange.Address, _
HtmlType:=xlHtmlStatic)
.Publish (True)
End With

'Read all data from the htm file into RangetoHTML
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
RangetoHTML = ts.readall
'Set fso = CreateObject("Scripting.FileSystemObject")
'Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
'GetBoiler = ts.readall
ts.Close
RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
"align=left x:publishsource=")

'Close TempWB
TempWB.Close savechanges:=False

'Delete the htm file we used in this function
Kill TempFile
Set ts = Nothing
Set fso = Nothing
Set TempWB = Nothing
End Function
...............................................................................................................................

4] Function:
Function GetBoiler(ByVal sFile As String) As String
'**** Kusleika
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function

PS:
I think I will be able to get the desired result these codes are modified/corrected in a proper way ..Awaiting Reply ..
Chao!!

Hey Thanks for replying ..I appreciate ..I am still gettin the same error:

Hey Rob,
I am getting the same error .Lines are:
Set OutApp = CreateObject("Outlook.Application")
.Body = "Hi Alis," & "vbNewline" & "This is to update your account status with us." & "vbNewline" & "Todays position is:" & 'vbNewline"

Dear Rob ,
I have used your earlier input but i got the same error " Type mismatch".I am sorry not to have wraped the code ..Here they are all of them .Also is the links I have used to get these codes .My codes below are mangled as i tried to work with them and failed ..attached is the plain excel sheet explaining the concept again ( i hope )

Code 1: Suggested by Gaiden,and amended as per your suggestion .

Please Login or Register  to view this content.

Procedures:

Please Login or Register  to view this content.

2] Procedure:

'To use the code that is presented in this section, first create a table on the active sheet with the following columns:

'* Column A: Names of the people

'* Column B: E-mail addresses

'* Column C: yes or no ( if the value is yes, an e-mail message is created)

'The following subroutine loops through each row on the active sheet and, if there is an e-mail address in column B and Yes in column C, an e-mail message for each person listed in column A is created that has a reminder

Please Login or Register  to view this content.

3// Functions:

Please Login or Register  to view this content.

.................................................................................................... ...........................

4] Function:

Please Login or Register  to view this content.

Links:
http://www.rondebruin.nl/sendmail.htm
http://www.rondebruin.nl/mail/importcontacts.htm
http://www.rondebruin.nl/mail/folder3/signature.htm
http://www.rondebruin.nl/mail/tips2.htm
http://www.rondebruin.nl/mail/folder3/row2.htm
I hope this helps
I am not able to get the original file i worked on as i deleted all in frustration ...

Hey Rob ,
When I try the code:

Please Login or Register  to view this content.

I get error with the line .Body = .Body & Range("f1:h2").Cells .This is when I run it with the sheet I just attached..

Hey Rob ,
attached is the Excel Sheet .I think this explains better What I am looking for ...Sorry for being so fussy ...

Hey Rob ,
First of all let me take this opportunity to express my gratitude for all your help and
thanks for the latest input ...I am gettin somewhere with ur help ...Still I have following problems :
Latest Queries Pending)
1/ To put Table in the body of E mail after initial reference to the account : :
Eg For Alex Current Position is different from Joe so on and so forth
Note 1: Heading has to be retained in all the e mail bodies:
Note2: I am losing the format of the table when copying the table to body of e mail .

2/ Insert My signature after the e mail body .
3/ Include Cc and Bcc in all the e mails
4/ Replace Yes with Active ,and No with Blocked in Cell E ,which strangly I am not able to do ..
5/ Link :http://www.rondebruin.nl/mail/folder3/message.htm

Please Login or Register  to view this content.

I have uploaded the file for reference ..
Regards
Alok

HTML Code: 

Correction:Note 1: Heading has to be retained in all the tables picked up from Excel file.

Take a look at this template http://www.rondebruin.nl/mail/outlooksheettemplate.htm

I guess the final code should read Like this

Please Login or Register  to view this content.

Problem Area:

I am not able to resolve the ...is this correct to copy the table in the body of the e mail.

HTML Code: 

..Awaiting inputs ..
Regards

Hey Rob,
I am able to resolve the problem upto some extent using the following code.I managed to generate E mail in the desired format .Now I am left with following queries:
1/ I am not able to change the account position in relation to names in the table:Eg For Alex Current Position is :

HTML Code: 

and for Joe ,It should copy as

HTML Code: 

which is not happening i am getting Alex's table even in Joe's e mail ,
and I am still I am losing the format of the table when copying the table to body of e mail .
2/ Insert My signature after the e mail body .
3/ Include Cc and Bcc in all the e mails
4/ Replace Yes with Active ,and No with Blocked
in Cell E ,which strangly I am not able to do ..

Please Login or Register  to view this content.

Please advice
Regards

Dear jaslake,
Thanks fr assisting .This works but with following problems.
1/I still lose the format of the table .Thw boderline are not prominently comin up
2/ Can you assist integrating my signature in the E mail or do I simply Replace it with "Nuttycongo" in the code.
3/ Yes= Account Is "Active"
and No= Account is "Blocked".
4/I want to replace Active/Blocked with yes/No in the active sheet as thats how it is formatted ,but when I am changing "Yes" to "Active" in the code and worksheet it seems not to work ..
@ Rob it's you who prompted me to explore all the way till here ...i wud have given up but for you ...I am still slogging to solve ...Off course with help from all of thre Guys in the forum ..

Cheers and many thanks ...

@ trucker10
Mate ...I will look it up ...
Many Thanks

Guys,
I appreciate your concern with he task ..I am really sorry to have kept u awake past your bedtime John..and Rob I think post 35 is well within your reach ..I have tried your latest code but it fails to detect "Workbooks.Open Filename:="C:\Users\Alok\Desktop\Revised Nutty v5.xls".Any Clue ..
Regards

Hey Rob ,
I am almost there,thanks to you and John ...I have formulated a code to hide table deficiencies (it's the only way i could have jiggled around considering my limited/ no knowledge of VB ...I am also attaching a sheet I want to format with the code provided by you ..for modification ...

Hey Rob,
Here is the code I modified ..to hide table abnormalities..

Please Login or Register  to view this content.

it's just a short cut as i have little time left now ..also attaching the sheet for you to review.

PS: I am not able to run the codes provided in @ 33 as i am not able to locate the workbook ..any inputs ...
Regards

Dear Rob,
I greatly appreciate your feedback .Yes you are right i did not put any code in nutty12 coz i am modifying it as per the work requirement but I am taking too much of time myself due to lack of know how .I was wrong in asking you to do it for me and i am sorry for that .let me try to do it my self first.I will miss this deadline but thats alright as I have learned to come this far and thats enough reward for me for this time around..I am not a spamer .This is a work requirement, I work in accounts receivables ,and i need to send atlest 150 -200 e mails in a day to companies that owe us money.I was thinking if i could get in the first trigger e mail fast to them this would give me sufficient time to reconcile accounts and do other meaningful activities.I have been learning VB for the past week or so only through this forum.I also went through books on VBA but all of it is greek to me at the moment ...Well thats about it ..This forum has been a teacher and a resource for me and i appreciate contributions from you and John...I am once again sorry if you have felt the way you have ...No excuses from my side I will do it myself and post the codes for you all wonderful guys to correct/amend ..no hard feelings ..
Regards

Dear Rob,
The post I was referring to is @ 30 by John and in attachment ;code you will find "C:\Documents and Settings\Administrator\Desktop\nuttycongo123\Revised Nutty v3.xls"..
Cheers..

Originally Posted by jaslake

Hi nuttycongo123

The attached addresses these issues
You'll need to change this line of code to insert your signature

Please Login or Register  to view this content.

Look at the link to see how to do that. If you're unable to sort it out, let us know.

Hey John I have managed to solve the Yes to Active ..transition in the code ,and it works fine,signature can wait and as far as formating goes I still have the "ignore" option as I will take time to understand it ..
...Thank you ..
Regards..

Dear Rob,
Please find attached ...I hope I explains all
Regards

It says runtime Error 9 subscription out of range ..
and the error highlight is

HTML Code: 

Regards

@

HTML Code: 

hey Rob I hugely appreciate what T10 posted and have gone through it as well ...downloading the temp wont have given me the chance to learn( by now you must have realised how rudimentry my skill level is ...HAHAHA),and even though I was short of time I decided to take a plunge ...and I feel very happy and satisfied now ...the mental stimulation is ...phew !! ...I will get there someday but for now the journey is simple amazing thanks to you ,John .Trucker and this Forum .
Regards

Yes I rectifed that ....Thanks..will keep you posted about the outlook project as I will work on it once I gety Home ...
Regards

VBA is just great when you can!
I stand by my truck and the road
I followed the thread, good job by Rob and John and yourself nuttycongo123

For your other file formats (I think!) it should be just a case of changing the numbers in the "cell.offset..." sections & the R1/R2 ranges to pick up the correct info.
How's it coming along?

Rob

I tried understanding the following code and tried editing it for nutty 12 sheet i am attaching here ,but the code just wont run:

Please Login or Register  to view this content.

I am not able to understand the R2 and Cell offset concet .Here is my observations:

HTML Code: 

I think I have to learn more abt cell.offset and range ..
Regards

a little play with the code
"<BR>Today's " & Date & " position is: " & cell.Offset(0, 5).Value & "<h3><font color =red > over the limit " & cell.Offset(0, 6).Value & ...................

Please Login or Register  to view this content.

Very Intresting input Trucker..I will try am modify the date format to Feb 2nd, 2011 ,will keep you posted ...thanks a ton
Regards

Originally Posted by nuttycongo123

I tried understanding the following code and tried editing it for nutty 12 sheet i am attaching here ,but the code just wont run:

Please Login or Register  to view this content.

I am not able to understand the R2 and Cell offset concet .Here is my observations:

HTML Code: 

I think I have to learn more abt cell.offset and range ..
Regards

Dear Rob,
I think the R1/R2 for sheet Nutty 12 will be Set R1 = Range("e1:k1")
Set R2 = cell.Offset(0, 8).Resize(1, 7)
Set rng = Union(R1, R2)..
Regards

This quote just wont run ...hmm No error msg either

Please Login or Register  to view this content.

I am tryin to run it with Nutty 12 which is attached
any advices ..Rob

John , i do step back , i'll be waiting .

John,Trucker 10 ,Rob,
Let me explain ...I have huge amount of data that needs to be manipulated and readjusted.There are 6000 Rows of data which keeps on changing on daily basis as these ar financial positions of our clients ...right now the process which you all are helping me through is the 1st part of the entire project .Latest posts by John and Trucker are really helpful coz they provide immense data management scope.John I will be around for a long time on this forum ... I am learning just by looking/editing/and committing mistakes .in the codes provided by you guys .I am really thankful for all your help ...but I am afraid the entire Journey is going to be long one for all of us ...Regards to all ..

Dear John and trucker 10 ,
Find attached the latest working I combined both the codes and this is the final ouput ...I still can't figure out
1/ how to put space between "Dear" and "Name".in HTMl
2/ Trucker i think Over limit shoud fetch the value as you rightly did in your earlier post sheet was nutty 4 i think ..but it is not comin up in this version ..
I have to rush for work now will try and sort out if I find time ...Thanks guys ..will keep you posted with updates and development ..
@ Rob ...haven't heard from you I hope all is well with you too..take care everone and
many Thanks once again ..
Ps: John I will try and give you more inputs today ,just to keep you intrested ..
regards

Hi Nutty ,

You can test it , take a look into the sheet .

Originally Posted by trucker10

Hi Nutty ,

You can test it , take a look into the sheet .

Thats amazing ..Thanks mate ...One more question why do you code cell as
" eCell.Offset" ..many thanks ..

Hey Rob ..good to hear from you,goodluck to you to for your project ...Enjoy your weekend ..Regards

Nutti , the code eRng and eCell was from ??? but its work also without eRng , eCell > just Rng , Cell , why we use eRng or eCell .
read it easier

Please Login or Register  to view this content.

Originally Posted by jaslake

Hi Nutty

I've been on many journeys in my 70 years...I know they can be long and tortuous. I also know that moving targets are hard to hit. Only you can decide what you want. Define your final product...we can then offer solutions.

Do you know your target? If so, please show us; we'll help you to write the code. If the target changes from day to day, you'll lose me.

Hey john I found a code you posted in 2009 for excel - outlook contact update ...and it ran as

Please Login or Register  to view this content.

do you have a worksheet l that can explain what this code does ...
I have to automate the contacts list in order to run along with the codes we all have been working on ..please advise

Hello J,

http://www.excelforum.com/outlook-pr...h-mapping.html

I also have a new problem ...Attached is the excel sheet and it is a statement of account ,i.e bills i have to receive and bills I have to pay ..Now I need your help in setting off the invoices against each other in a best possible way ..Rule invoices should look to knock off with each other and return a value = 0 ,if not possible they should return the next closest value ..The main aim is to clean the mess (invoices) more the better ...John I have two 3 assignments where I need your help ...is it possible for you to devote some time ...Details are :

1/ Comparision Of two Excel sheets
2/ Formating the outcome in a specific way to deliver a summary report ..
Regards

Not sure if this is the same issue from when this first started but i have 2 codes here.

the first is for your default email program (i'm using thunderbird)

Please Login or Register  to view this content.

the second is for outlook only although i have not tested it.

Please Login or Register  to view this content.