Anomandaris
04-21-2009, 06:47 AM
Hi guys I have this macro that sends email from lotus notes, I need to change it so i can send the mail using outlook
What do i need to change here?
Sub FCMSend()
On Error Resume Next
Dim strbody As String, strsubject As String, first As String, _
second As String, third As String, sendto As String
Dim fourth As String
Dim tag1 As String
Dim intpr As Integer
Dim x As Integer
Dim y As Integer
Dim fifth As String
Dim saveit As String
Dim Recipient As String
Dim tagbody As String
Dim z As Integer
Dim refEnd As Integer
Dim RefST As String
Dim mail2 As String
Dim i As Integer
Dim buysell As String
Dim com As String
com = Sheet1.Range("x19")
intpr = Sheet6.Cells(3, 6).End(xlToRight).Column - 5
refEnd = Sheet1.Cells(18, 7).End(xlDown).row
For x = 8 To 16
If Sheet6.Cells(x, 4) <> 0 Then
tag1 = Sheet6.Cells(x, 4)
For z = 19 To refEnd
If Sheet1.Cells(z, 4) = tag1 Then
RefST = RefST & Sheet1.Cells(z, 7) & Chr(9) & Chr(9) & Sheet1.Cells(z, 5) & Chr(13)
Else
End If
Next
For y = 6 To intpr + 6
If Sheet6.Cells(x, y) <> 0 Then
tagbody = tagbody & Sheet6.Cells(3, y) & Chr(9) & Chr(9) & Sheet6.Cells(x, y) & Chr(10)
Else
End If
Next
second = "Hello" & Chr(13) & Chr(13) & "Confirming the following trades" & Chr(13) & Chr(13)
third = "Qty" & Chr(9) & "Average price" & Chr(9) & Chr(9) & Chr(9) & "Contract" & Chr(13)
fourth = Sheet1.Cells(x - 4, 36) & Chr(9) & Sheet6.Cells(x, 82) & Chr(9) & Chr(9) & Chr(9) & Sheet1.Range("B19") & Chr(9) & Chr(13) & Chr(10) & Chr(13)
fifth = Chr(13) & "Price" & Chr(9) & Chr(9) & "Qty" & Chr(10) & Chr(10)
If fourth = "" Then
fourth = "1" & Chr(9) & Sheet6.Range("g3") & Chr(9) & Chr(9) & Chr(9) & Sheet1.Range("B19") & Chr(9) & Chr(13) & Chr(10) & Chr(13)
Else
fourth = fourth
End If
If Sheet6.Range("f4") = 0 Then
tagbody = Sheet6.Range("g3") & Chr(9) & Chr(9) & Sheet6.Range("g4") & Chr(10)
Else
tagbody = tagbody
End If
strbody = second & third & fourth & "Ref" & Chr(9) & Chr(9) & "Qty" & Chr(13) & RefST & fifth & tagbody
Else
End
End If
If tag1 = "COM" Then
sendto = com
End If
If tag1 = "P" Then
sendto = "P random"
End If
If tag1 = "F" Then
sendto = "F random"
End If
If tag1 = "M" Then
sendto = "M random"
End If
If tag1 = "COW" Then
sendto = "Cow brain"
End If
If tag1 = "PEA" Then
sendto = "Peabrain"
End If
If tag1 = "X" Then
sendto = "X men"
End If
If tag1 = "B" Then
sendto = "barking"
End If
If tag1 = "RR" Then
sendto = "RoR"
End If
For i = 1 To 2
If i = 1 Then
mail2 = sendto
Else
mail2 = Sheet1.Range("Z10")
End If
MsgBox "Sending to " & mail2, vbInformation, "bogus info"
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
'Start a session to notes
Set Session = CreateObject("Notes.NotesSession")
'Next line only works with 5.x and above. Replace password with your password
' Session.Initialize ("password")
'Get the sessions username and then calculate the mail file name
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
'Open the mail database in notes
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen = True Then
'Already open for mail
Else
Maildb.OPENMAIL
End If
'Set up the new mail document
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.sendto = mail2
MailDoc.Subject = " COM trades " & tag1 & Chr(32) & Sheet1.Range("B19")
MailDoc.body = strbody
MailDoc.SaveMessageOnSend = saveit
'Set up the embedded object and attachment and attach it
'If ATTACHMENT <> "" Then
' Set AttachME = MailDoc.CreateRichTextItem("Attachment")
' Set EmbedObj = AttachME.EmbedObject(1454, "", ATTACHMENT, "Attachment")
' MailDoc.CreateRichTextItem ("Attachment")
' End If
'Send the document
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
MailDoc.send 0, Recipient
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
Next
tag1 = ""
tagbody = ""
RefST = ""
tag1 = ""
Next
End Sub
i think i would have to change the last part of the code where its obviously opening up lotusnotes but im not sure how, any ideas? Thanks a lot in advance
What do i need to change here?
Sub FCMSend()
On Error Resume Next
Dim strbody As String, strsubject As String, first As String, _
second As String, third As String, sendto As String
Dim fourth As String
Dim tag1 As String
Dim intpr As Integer
Dim x As Integer
Dim y As Integer
Dim fifth As String
Dim saveit As String
Dim Recipient As String
Dim tagbody As String
Dim z As Integer
Dim refEnd As Integer
Dim RefST As String
Dim mail2 As String
Dim i As Integer
Dim buysell As String
Dim com As String
com = Sheet1.Range("x19")
intpr = Sheet6.Cells(3, 6).End(xlToRight).Column - 5
refEnd = Sheet1.Cells(18, 7).End(xlDown).row
For x = 8 To 16
If Sheet6.Cells(x, 4) <> 0 Then
tag1 = Sheet6.Cells(x, 4)
For z = 19 To refEnd
If Sheet1.Cells(z, 4) = tag1 Then
RefST = RefST & Sheet1.Cells(z, 7) & Chr(9) & Chr(9) & Sheet1.Cells(z, 5) & Chr(13)
Else
End If
Next
For y = 6 To intpr + 6
If Sheet6.Cells(x, y) <> 0 Then
tagbody = tagbody & Sheet6.Cells(3, y) & Chr(9) & Chr(9) & Sheet6.Cells(x, y) & Chr(10)
Else
End If
Next
second = "Hello" & Chr(13) & Chr(13) & "Confirming the following trades" & Chr(13) & Chr(13)
third = "Qty" & Chr(9) & "Average price" & Chr(9) & Chr(9) & Chr(9) & "Contract" & Chr(13)
fourth = Sheet1.Cells(x - 4, 36) & Chr(9) & Sheet6.Cells(x, 82) & Chr(9) & Chr(9) & Chr(9) & Sheet1.Range("B19") & Chr(9) & Chr(13) & Chr(10) & Chr(13)
fifth = Chr(13) & "Price" & Chr(9) & Chr(9) & "Qty" & Chr(10) & Chr(10)
If fourth = "" Then
fourth = "1" & Chr(9) & Sheet6.Range("g3") & Chr(9) & Chr(9) & Chr(9) & Sheet1.Range("B19") & Chr(9) & Chr(13) & Chr(10) & Chr(13)
Else
fourth = fourth
End If
If Sheet6.Range("f4") = 0 Then
tagbody = Sheet6.Range("g3") & Chr(9) & Chr(9) & Sheet6.Range("g4") & Chr(10)
Else
tagbody = tagbody
End If
strbody = second & third & fourth & "Ref" & Chr(9) & Chr(9) & "Qty" & Chr(13) & RefST & fifth & tagbody
Else
End
End If
If tag1 = "COM" Then
sendto = com
End If
If tag1 = "P" Then
sendto = "P random"
End If
If tag1 = "F" Then
sendto = "F random"
End If
If tag1 = "M" Then
sendto = "M random"
End If
If tag1 = "COW" Then
sendto = "Cow brain"
End If
If tag1 = "PEA" Then
sendto = "Peabrain"
End If
If tag1 = "X" Then
sendto = "X men"
End If
If tag1 = "B" Then
sendto = "barking"
End If
If tag1 = "RR" Then
sendto = "RoR"
End If
For i = 1 To 2
If i = 1 Then
mail2 = sendto
Else
mail2 = Sheet1.Range("Z10")
End If
MsgBox "Sending to " & mail2, vbInformation, "bogus info"
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
'Start a session to notes
Set Session = CreateObject("Notes.NotesSession")
'Next line only works with 5.x and above. Replace password with your password
' Session.Initialize ("password")
'Get the sessions username and then calculate the mail file name
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
'Open the mail database in notes
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen = True Then
'Already open for mail
Else
Maildb.OPENMAIL
End If
'Set up the new mail document
Set MailDoc = Maildb.CreateDocument
MailDoc.Form = "Memo"
MailDoc.sendto = mail2
MailDoc.Subject = " COM trades " & tag1 & Chr(32) & Sheet1.Range("B19")
MailDoc.body = strbody
MailDoc.SaveMessageOnSend = saveit
'Set up the embedded object and attachment and attach it
'If ATTACHMENT <> "" Then
' Set AttachME = MailDoc.CreateRichTextItem("Attachment")
' Set EmbedObj = AttachME.EmbedObject(1454, "", ATTACHMENT, "Attachment")
' MailDoc.CreateRichTextItem ("Attachment")
' End If
'Send the document
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
MailDoc.send 0, Recipient
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
Next
tag1 = ""
tagbody = ""
RefST = ""
tag1 = ""
Next
End Sub
i think i would have to change the last part of the code where its obviously opening up lotusnotes but im not sure how, any ideas? Thanks a lot in advance