Copy excel content and email it using outlook

blablabla

New Member
So, I want to copy content from an excel file and mail that content, the important part is that my excel contains different color and text and i want the same format in email.
What options do i have ?
Dont be so harsh on me, I am new to blue prism.
 

Shweta

Active Member
You can achieve it via running Macro. The below macro should return the result, that you are expecting.

Before running the macro, Select the data using "Select" action of MS Excel VBO.

Sub Screenshot()
Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
path_Paint = "C:\Windows\System32\mspaint.exe"
paintID = Shell(path_Paint, vbNormalFocus)
Application.Wait Now + TimeValue("00:00:04")
Application.SendKeys "^(v)", True
Application.Wait Now + TimeValue("00:00:04")
Application.SendKeys "^(s)", True
Application.Wait Now + TimeValue("00:00:05")
Application.SendKeys "C:\Blueprism\DateFolder\Test.png" //C:\Blueprism\DateFolder : Destination path, where you are saving image and Test.png is the name of the saved file.
Application.SendKeys "%ie~"

End Sub

In Send Email, give the path of saved file, in Attachment collection.
 

VJR

Well-Known Member
Hi blablabla,

Since you're already making use of Excel and Outlook lets make use of Excel's inbuilt function called as MailEnvelope.

- Go to the Studio and under the Objects section find the MS Excel VBO and open it
- Go to the Select tab. Among the several tabs, right click on the 'Select' tab name and choose 'Duplicate' and give it a name like 'Select Cells & Email'.
- Double click the Code stage and give it a name (either same or different than) 'Select Cells & Email'.
- Go to the Code tab

Dim wb, ws As Object
Dim excel, sheet, range, SendRange As Object

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(Reference)
range.Select()

SendRange = excel.Selection

With SendRange
wb.EnvelopeVisible = True

With .Parent.MailEnvelope


'.Introduction = "This is a test mail from BP" 'Optional


With .Item
.To = "xyz@someemail.com"
.CC = ""
.BCC = ""
.Subject = "Test subject"
.Display 'comment this if you do not want to Preview the email before sending
'.Send 'Uncomment this for sending the email
End With


End With
End With


Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
ws = Nothing
excel = Nothing
sheet = Nothing
range = Nothing
SendRange = Nothing
End Try

- The only code different than what is already there is the one is bold. So either Copy-Paste this entire code or add only the marked ones.
- Hit Ok to Close the window.
- Again, right click on the new tab name and choose Publish.
- Click Save, Reset, Refresh available on the top toolbars.
- Now go to your process window where you are designing your process diagram.
- On the top hit Refresh so that the newly created action is reflected. If it doesn't reflect then you need to close and re-open Blue Prism once.
- Add a new action stage, select the newly created action in the dropdown and give its parameters.
In the Cell Reference parameter select the range that you want to be emailed.

If you do not know how many rows are present on the sheet, find the last row using 'Get Number of rows' action into a data item say 'LastRow'. Then use "A" & [LastRow] & ":C" & [LastRow]

View attachment 1542961713718.png

- The Process diagram and the Preview of the email with the Excel sheet now looks like this after running the process.
View attachment 1542962046082.png

- The preview of the email is seen because .Display is uncommented and .Send is commented (single quote) in the above code.
Refer to the notes mentioned in the above code around the Display and the Send.

- Once you are fine with the working of your new object action you can permanently comment the .Display and uncomment the .Send and use the new action from the process.

- The Introduction, To, CC, BCC and Subject can be added as Input parameters to this Object to make it a customised and re-usable one which should always be the case. You can find how to add the Input/Output parameters in the Blue Prism documentation.

I've tried to add the instructions from a newbie perspective. If something is not working or unclear then do post back with full details.
 

VJR

Well-Known Member
Since you asked what options you have, you can also send the sheet data as an image file attachment via email.
If that works for you then you can take a look at post #14 which saves the sheet data as an image file on the disk.
Once you have this file you can use the email VBO (either Outlook VBO or MapiEx whichever you are using), then attach this file to your email and send it across.
Or if you want to embed the image rather than only an attachment then take a look at post #6 here. Hope I did not overwhelm you :), but you can gauge the options and select which one sounds feasible.
 

finocia

New Member
Hai VJR ,
The post#14 you have mentioned is blocked ,couldn't see any contents,Please tell me how to get the excel data as image

Thanks,
Finocia.
 

luke.ruben

New Member
Hi blablabla,

Since you're already making use of Excel and Outlook lets make use of Excel's inbuilt function called as MailEnvelope.

- Go to the Studio and under the Objects section find the MS Excel VBO and open it
- Go to the Select tab. Among the several tabs, right click on the 'Select' tab name and choose 'Duplicate' and give it a name like 'Select Cells & Email'.
- Double click the Code stage and give it a name (either same or different than) 'Select Cells & Email'.
- Go to the Code tab

Dim wb, ws As Object
Dim excel, sheet, range, SendRange As Object

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
range = sheet.Range(Reference)
range.Select()

SendRange = excel.Selection

With SendRange
wb.EnvelopeVisible = True

With .Parent.MailEnvelope


'.Introduction = "This is a test mail from BP" 'Optional


With .Item
.To = "xyz@someemail.com"
.CC = ""
.BCC = ""
.Subject = "Test subject"
.Display 'comment this if you do not want to Preview the email before sending
'.Send 'Uncomment this for sending the email
End With


End With
End With


Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
wb = Nothing
ws = Nothing
excel = Nothing
sheet = Nothing
range = Nothing
SendRange = Nothing
End Try

- The only code different than what is already there is the one is bold. So either Copy-Paste this entire code or add only the marked ones.
- Hit Ok to Close the window.
- Again, right click on the new tab name and choose Publish.
- Click Save, Reset, Refresh available on the top toolbars.
- Now go to your process window where you are designing your process diagram.
- On the top hit Refresh so that the newly created action is reflected. If it doesn't reflect then you need to close and re-open Blue Prism once.
- Add a new action stage, select the newly created action in the dropdown and give its parameters.
In the Cell Reference parameter select the range that you want to be emailed.

If you do not know how many rows are present on the sheet, find the last row using 'Get Number of rows' action into a data item say 'LastRow'. Then use "A" & [LastRow] & ":C" & [LastRow]

View attachment 2599

- The Process diagram and the Preview of the email with the Excel sheet now looks like this after running the process.
View attachment 2600

- The preview of the email is seen because .Display is uncommented and .Send is commented (single quote) in the above code.
Refer to the notes mentioned in the above code around the Display and the Send.

- Once you are fine with the working of your new object action you can permanently comment the .Display and uncomment the .Send and use the new action from the process.

- The Introduction, To, CC, BCC and Subject can be added as Input parameters to this Object to make it a customised and re-usable one which should always be the case. You can find how to add the Input/Output parameters in the Blue Prism documentation.

I've tried to add the instructions from a newbie perspective. If something is not working or unclear then do post back with full details.

Hey Jose I got the error : Failed to select row: The given key was not present in the dictionary.
Also I want To know how do i add To, CC, BCC and Subject
 

coliin7

New Member
Hi blablabla,

Since you're already making use of Excel and Outlook lets make use of Excel's inbuilt function called as MailEnvelope.

- Go to the Studio and under the Objects section find the MS Excel VBO and open it
- Go to the Select tab. Among the several tabs, right click on the 'Select' tab name and choose 'Duplicate' and give it a name like 'Select Cells & Email'.
- Double click the Code stage and give it a name (either same or different than) 'Select Cells & Email'.
- Go to the Code tab

Dim wb, ws As Object
Dim excel, sheet, range, SendRange As Object

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet
rango = hoja. Rango (Referencia)
range.Select ()

SendRange = excel.Selection

Con SendRange
wb.EnvelopeVisible = True

Con .Parent.MailEnvelope


'.Introduction = "Este es un correo de prueba de BP"' Opcional


Con .Item
.To = " xyz@someemail.com "
.CC = ""
.BCC = ""
.Subject = "Test subject"
.Display 'comenta esto si no quieres obtener una vista previa del correo electrónico antes de enviarlo
'Enviar' Descomenta esto para enviar el correo electrónico
Terminar con


Terminar con
Terminar con


Éxito = verdadero

Captura e como excepción
Éxito = falso
Mensaje = e.Message
Finalmente
wb = nada
ws = nada
excel = nada
hoja = nada
rango = nada
SendRange = Nada
Intento final

- El único código diferente al que ya existe es el que está en negrita. Entonces, copie y pegue todo este código o agregue solo los marcados.
- Presiona Ok para cerrar la ventana.
- Nuevamente, haga clic derecho en el nuevo nombre de la pestaña y seleccione Publicar.
- Haga clic en Guardar, Restablecer, Actualizar disponible en las barras de herramientas superiores.
- Ahora vaya a la ventana de proceso donde está diseñando su diagrama de proceso.
- En la parte superior, haz clic en Actualizar para que se refleje la acción recién creada. Si no se refleja, debe cerrar y volver a abrir Blue Prism una vez.
- Agregue una nueva etapa de acción, seleccione la acción recién creada en el menú desplegable y proporcione sus parámetros.
En el parámetro Referencia de celda, seleccione el rango que desea que se envíe por correo electrónico.

Si no sabe cuántas filas hay en la hoja, busque la última fila con la acción 'Obtener número de filas' en un elemento de datos que diga 'Última fila'. Luego use "A" y [LastRow] y ": C" y [LastRow]

[ATTACH = full] 2599 [/ ATTACH]

- El diagrama de proceso y la vista previa del correo electrónico con la hoja de Excel ahora se ve así después de ejecutar el proceso.
[ATTACH = full] 2600 [/ ATTACH]

- La vista previa del correo electrónico se ve porque .Display no está comentado y .Send se comenta (comilla simple) en el código anterior.
Consulte las notas mencionadas en el código anterior alrededor de la pantalla y el envío.

- Una vez que esté bien con el funcionamiento de su nueva acción de objeto, puede comentar permanentemente el .Display y descomentar el .Enviar y usar la nueva acción del proceso.

- La Introducción, Para, CC, CCO y Asunto se pueden agregar como parámetros de entrada a este Objeto para que sea personalizado y reutilizable, lo que siempre debería ser el caso. Puede encontrar cómo agregar los parámetros de Entrada / Salida en la documentación de Blue Prism.

He tratado de agregar las instrucciones desde una perspectiva de novato. Si algo no funciona o no está claro, vuelva a publicar con todos los detalles. [/ QUOTE]




¿Cómo estás? con esta misma acción podre agregarle codigo para poder adjuntar el archivo de excel tambien? muchisimas gracias
 
Top